Hello, We have a plsql procedure in which it updates multiple columns of
the global temporary table after loading it in different steps. It's
updated for different filter criteria and each of these UPDATES are running
for hours even if they are simple ones. I am trying to see if it's possible
to do it in an efficient fashion by combining all different updates into
one UPDATE statement or any other possible method? One initial thought was
to do it as part of the INSERT itself rather than updating it through
multiple statements at the later stage , but then I see the data
load/INSERT is happening for multiple if/else conditions which may not be
straight enough.
Attached is the sql monitor for few of the long running UPDATES. And i am
seeing this is not really spending time for updating rows but rather in
finding the eligible rows for update i.e. the SELECT part of it is actually
taking time. So wanted to understand how these UPDATES can be done faster?
Its version 11.2.0.4 of Oracle Exadata-X5.
Regards
Lok
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)
