UPDATE GTT_TAB TMP SET (c1, c2, c3, c4,c5.. c11) = ( (SELECT col1,col2,..col11 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND TMP.CIND = 'Y' AND P.PART_DT = TMP.PART_DT AND P.CODE = 'XX')) Global Information ------------------------------ Status : DONE Instance ID : 1 SQL Execution ID : 16784210 Execution Started : 05/02/2021 10:19:15 First Refresh Time : 05/02/2021 10:19:18 Last Refresh Time : 05/02/2021 11:26:39 Duration : 4044s PLSQL Entry Ids (Object/Subprogram) : 87734,1 PLSQL Current Ids (Object/Subprogram) : 87734,1 Global Stats ================================================================================= | Elapsed | Cpu | IO | Concurrency | Cluster | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | ================================================================================= | 4465 | 1412 | 3052 | 0.57 | 0.72 | 79M | 9M | 74GB | ================================================================================= SQL Plan Monitoring Details (Plan Hash Value=1406665007) ==================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ==================================================================================================================================================================================================== | 0 | UPDATE STATEMENT | | | | 1235 | +2153 | 1 | 0 | | | 0.07 | Cpu (1) | | | | | | | | | | | | | | reliable message (2) | | 1 | UPDATE | GTT_TAB | | | 4044 | +1 | 1 | 0 | | | 10.62 | Cpu (429) | | 2 | TABLE ACCESS STORAGE FULL | GTT_TAB | 8G | 381K | 4042 | +3 | 1 | 17M | 4467 | 4GB | 0.72 | Cpu (20) | | | | | | | | | | | | | | cell multiblock physical read (9) | | 3 | FILTER | | | | 4042 | +3 | 17M | 8M | | | 0.07 | Cpu (3) | | 4 | PARTITION RANGE SINGLE | | 1 | 13 | 4042 | +3 | 8M | 8M | | | 0.12 | Cpu (5) | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | PRODUCT_TAB | 1 | 13 | 4043 | +2 | 8M | 8M | 6M | 45GB | 55.79 | gc cr grant 2-way (1) | | | | | | | | | | | | | | Cpu (292) | | | | | | | | | | | | | | cell single block physical read (1961) | | 6 | INDEX RANGE SCAN | PROD_TAB_IX1 | 1 | 4 | 4042 | +3 | 8M | 8M | 3M | 25GB | 32.60 | Cpu (218) | | | | | | | | | | | | | | cell single block physical read (1099) | ==================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:B1='Y') 5 - filter("P"."PART_DT"=:B1) 6 - access("P"."TID"=:B1 AND "P"."CODE"='XX') ************************************************** UPDATE GTT_TAB TMP SET EASTATUS = (SELECT COL14 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT AND P.CODE = 'YY') ( and another similar update exists as below UPDATE GTT_TAB SET RNUM = (SELECT COL1 FROM METHOD_TAB WHERE PART_DT = GTT_TAB.PART_DT AND TID = GTT_TAB.TID AND MCODE = 'MM') ) Global Information ------------------------------ Status : DONE Instance ID : 1 SQL Execution ID : 16784213 Execution Started : 05/02/2021 07:59:26 First Refresh Time : 05/02/2021 07:59:29 Last Refresh Time : 05/02/2021 09:39:49 Duration : 6023s PLSQL Entry Ids (Object/Subprogram) : 87734,1 PLSQL Current Ids (Object/Subprogram) : 87734,1 Global Stats ================================================================================= | Elapsed | Cpu | IO | Concurrency | Cluster | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | ================================================================================= | 6540 | 1627 | 4913 | 0.30 | 0.09 | 102M | 11M | 87GB | ================================================================================= SQL Plan Monitoring Details (Plan Hash Value=3209483866) =================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | =================================================================================================================================================================================================== | 0 | UPDATE STATEMENT | | | | 5122 | +122 | 1 | 0 | | | 0.15 | Cpu (5) | | | | | | | | | | | | | | enq: US - contention (1) | | | | | | | | | | | | | | reliable message (3) | | 1 | UPDATE | GTT_TAB | | | 6022 | +2 | 1 | 0 | | | 6.80 | Cpu (409) | | 2 | TABLE ACCESS STORAGE FULL | GTT_TAB | 147K | 377 | 6021 | +3 | 1 | 17M | 5632 | 3GB | 0.55 | Cpu (25) | | | | | | | | | | | | | | cell multiblock physical read (8) | | 3 | PARTITION RANGE SINGLE | | 1 | 13 | 5922 | +102 | 17M | 7M | | | 0.23 | Cpu (14) | | 4 | TABLE ACCESS BY LOCAL INDEX ROWID | PRODUCT_TAB | 1 | 13 | 5931 | +93 | 17M | 7M | 6M | 43GB | 44.90 | Cpu (283) | | | | | | | | | | | | | | cell single block physical read (2419) | | 5 | INDEX RANGE SCAN | PROD_TAB_IX1 | 1 | 4 | 6023 | +1 | 17M | 7M | 5M | 41GB | 47.36 | Cpu (356) | | | | | | | | | | | | | | cell single block physical read (2494) | =================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("P"."PART_DT"=:B1) 5 - access("P"."TID"=:B1 AND "P"."CODE"='YY') ************************************************** UPDATE GTT_TAB TMP SET (FS_REQ, FS_RES) = ( (SELECT 'ZZ' || COL4, COL5 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT AND P.CODE = 'ZZ')) Global Information ------------------------------ Status : DONE Instance ID : 1 SQL Execution ID : 16784202 Execution Started : 05/02/2021 09:39:54 First Refresh Time : 05/02/2021 09:39:58 Last Refresh Time : 05/02/2021 10:19:11 Duration : 2357s PLSQL Entry Ids (Object/Subprogram) : 87734,1 PLSQL Current Ids (Object/Subprogram) : 87734,1 Global Stats ================================================================================= | Elapsed | Cpu | IO | Concurrency | Cluster | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | ================================================================================= | 2581 | 968 | 1613 | 0.02 | 0.01 | 94M | 5M | 43GB | ================================================================================= SQL Plan Monitoring Details (Plan Hash Value=3209483866) =================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | =================================================================================================================================================================================================== | 0 | UPDATE STATEMENT | | | | 863 | +590 | 1 | 0 | | | 0.17 | Cpu (3) | | | | | | | | | | | | | | reliable message (1) | | 1 | UPDATE | GTT_TAB | | | 2354 | +4 | 1 | 0 | | | 14.82 | Cpu (349) | | 2 | TABLE ACCESS STORAGE FULL | GTT_TAB | 8G | 381K | 2354 | +4 | 1 | 17M | 5146 | 4GB | 1.32 | Cpu (21) | | | | | | | | | | | | | | cell multiblock physical read (10) | | 3 | PARTITION RANGE SINGLE | | 1 | 13 | 2168 | +136 | 17M | 0 | | | 0.68 | Cpu (16) | | 4 | TABLE ACCESS BY LOCAL INDEX ROWID | PRODUCT_TAB | 1 | 13 | 1576 | +763 | 17M | 0 | | | 0.34 | Cpu (8) | | 5 | INDEX RANGE SCAN | PROD_TAB_IX1 | 1 | 4 | 2358 | +0 | 17M | 0 | 5M | 39GB | 82.68 | Cpu (318) | | | | | | | | | | | | | | cell single block physical read (1629) | =================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("P"."PART_DT"=:B1) 5 - access("P"."TID"=:B1 AND "P"."CODE"='ZZ') *************** Another two Updates exists areas below but i have not captured the sqlmonitor for below******************** UPDATE GTT_TAB TMP SET ANUM = (SELECT col5 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT AND P.PART_DT BETWEEN :B2 AND :B1 AND P.CODE = 'AA' AND P.col5 IS NOT NULL) WHERE EXISTS (SELECT col5 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT AND P.PART_DT BETWEEN :B2 AND :B1 AND P.CODE = 'AA' AND P.col5 IS NOT NULL) UPDATE GTT_TAB TMP SET EXP_MMYY = (SELECT COL6 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT AND P.PART_DT BETWEEN :B2 AND :B1 AND P.CODE = 'AA' AND P.COL6 IS NOT NULL) WHERE EXISTS (SELECT COL6 FROM PRODUCT_TAB P WHERE P.TID = TMP.TID AND P.PART_DT = TMP.PART_DT AND P.PART_DT BETWEEN :B2 AND :B1 AND P.CODE = 'AA' AND P.COL6 IS NOT NULL)