We are using version 12.1.0.2.0 of oracle. We have a DELETE query which is taking longer few of the times. And looking at the sqlmonitor of two of the occasion, it seems to be the elapsed time is varying depending on the number of records in stage table STG_TAB1. Below is the two of the instances in one case it ran fast in other it took more time. We want to improve the performance of this DELETE query. I went through the DBA_HIST_ACTIVE_SESS_HISTORY when this query ran slower, i see 75% of the time has been spent on step- 1 i.e. DELETE itself and from the ASH i see grouping by top current_obj# i see those are the TABLE itself(waiting on db file sequential read) at the top along with three indexes(with ON CPU) on those exists on this stage table-STG_TAB1.
Team is saying the parallel execution is causing bottleneck, i don't see any wait event supporting that thought, but yes this query is automatically going for parallelism because table - TAB2 is having degree defined as-4 and i think its since long back, so we were thinking to remove that degree, if that will not cause any harm. And i had another question, if the co-related sub query is anyway causing harm for this delete and can be modified to do better?
The records in stage table varies , and below is the current stats on the stage table STG_TAB1 and TAB2. Table TAB2 is list partitioned on column PART_KEY. Index STG_TAB1_IX2 is on STG_TAB1(PART_KEY) index STG_TAB1_IX3 is on STG_TAB1(SID,RIR).
Table Stats:-
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN DEGREE
TAB2 48829447 2210563 0 0 313 4
STG_TAB1 2735285 582492 0 0 284 1
Partition stats for TAB2:-
TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
TAB2 TAB2_P5 0 0 0 0 0
TAB2 TAB2_P4 536798 24757 0 0 302
TAB2 TAB2_P3 17272993 781639 0 0 302
TAB2 TAB2_P2 15950366 721573 0 0 300
TAB2 TAB2_P1 15069290 682594 0 0 302
DELETE FROM STG_TAB1
WHERE (SID, RIR) IN
(SELECT A.SID, A.RIR
FROM TAB2 A, STG_TAB1 B
WHERE A.SID = B.SID
AND A.RIR = B.RIR
AND A.PART_KEY = B.PART_KEY)
************Slow execution**********************
Global Information
------------------------------
Status : DONE
Instance ID : 1
Execution Started : 09/15/2018 03:46:49
First Refresh Time : 09/15/2018 03:46:52
Last Refresh Time : 09/15/2018 04:30:13
Duration : 2604s
Global Stats
===============================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
===============================================================================================================
| 2755 | 1377 | 1363 | 1.93 | 0.00 | 14 | 51M | 387K | 16GB | 17144 | 2GB |
===============================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
=================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
=================================================================================================================================================================
| PX Coordinator | QC | | 2273 | 1231 | 1031 | 1.93 | 0.00 | 8.45 | 50M | 347K | 3GB | | . | |
| p008 | Set 1 | 1 | 47 | 13 | 30 | | | 4.14 | | 2800 | 328MB | 2800 | 328MB | |
| p009 | Set 1 | 2 | 43 | 14 | 29 | | | | | 2800 | 328MB | 2800 | 328MB | |
| p00a | Set 1 | 3 | 43 | 14 | 29 | | | | | 2800 | 328MB | 2800 | 328MB | |
| p00b | Set 1 | 4 | 107 | 42 | 65 | | | | 129K | 9002 | 1GB | 5151 | 604MB | |
| p00c | Set 2 | 1 | 242 | 62 | 180 | | | 0.92 | 1M | 22615 | 10GB | 3593 | 421MB | |
| p00d | Set 2 | 2 | 0.01 | 0.01 | | | | | | | . | | . | |
| p00e | Set 2 | 3 | 0.01 | 0.01 | | | | | | | . | | . | |
| p00f | Set 2 | 4 | 0.01 | 0.01 | | | | | | | . | | . | |
=================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4030684647)
==========================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
==========================================================================================================================================================================================================
| 0 | DELETE STATEMENT | | | | 2269 | +336 | 1 | 0 | | | | | | | | |
| 1 | DELETE | STG_TAB1 | | | 2271 | +335 | 1 | 0 | 347K | 3GB | | | | | | |
| 2 | PX COORDINATOR | | | | 2603 | +2 | 9 | 4M | 3 | 24576 | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 3M | 776K | 2268 | +337 | 4 | 4M | | | | | | | | |
| 4 | HASH JOIN | | 3M | 776K | 2468 | +137 | 4 | 4M | 11200 | 1GB | 11200 | 1GB | 469M | 1G | | |
| 5 | PX RECEIVE | | 3M | 159K | 137 | +137 | 4 | 28M | | | | | | | | |
| 6 | PX SEND BROADCAST | :TQ10001 | 3M | 159K | 98 | +137 | 4 | 28M | | | | | | | | |
| 7 | PX SELECTOR | | | | 98 | +137 | 4 | 7M | | | | | | | | |
| 8 | TABLE ACCESS FULL | STG_TAB1 | 3M | 159K | 135 | +136 | 4 | 7M | 12626 | 4GB | | | | | | |
| 9 | VIEW | VW_NSO_1 | 3M | 618K | 858 | +337 | 4 | 4M | | | | | | | | |
| 10 | HASH UNIQUE | | 3M | 618K | 921 | +274 | 4 | 4M | | | | | 257M | | | |
| 11 | PX RECEIVE | | 3M | 618K | 61 | +274 | 4 | 4M | | | | | | | | |
| 12 | PX SEND HASH | :TQ10002 | 3M | 618K | 196 | +137 | 4 | 4M | | | | | | | | |
| 13 | HASH UNIQUE | | 3M | 618K | 196 | +137 | 4 | 4M | | | | | 2M | | | |
| 14 | HASH JOIN SEMI | | 3M | 586K | 307 | +27 | 4 | 4M | 3593 | 421MB | 3593 | 421MB | 199M | 472M | | |
| 15 | PX RECEIVE | | 3M | 40351 | 45 | +27 | 4 | 7M | | | | | | | | |
| 16 | PX SEND PARTITION (KEY) | :TQ10000 | 3M | 40351 | 109 | +27 | 4 | 7M | | | | | | | | |
| 17 | PX SELECTOR | | | | 109 | +27 | 4 | 7M | | | | | | | | |
| 18 | VIEW | index$_join$_003 | 3M | 40351 | 109 | +27 | 4 | 7M | | | | | | | | |
| 19 | HASH JOIN | | | | 131 | +5 | 4 | 7M | 2351 | 276MB | 2351 | 276MB | 197M | 308M | | |
| 20 | INDEX FAST FULL SCAN | STG_TAB1_IX2 | 3M | 10948 | 20 | +4 | 4 | 7M | 2891 | 256MB | | | | | | |
| 21 | INDEX FAST FULL SCAN | STG_TAB1_IX3 | 3M | 27717 | 35 | +23 | 1 | 7M | 960 | 654MB | | | | | | |
| 22 | PX PARTITION LIST ALL | | 44M | 545K | 233 | +73 | 1 | 16M | | | | | | | | |
| 23 | TABLE ACCESS FULL | TAB2 | 44M | 545K | 235 | +71 | 2 | 16M | 6396 | 6GB | | | | | | |
==========================================================================================================================================================================================================
******* FAST Execution ************************
Global Information
------------------------------
Status : DONE
Instance ID : 1
SQL Execution ID : 16777226
Execution Started : 09/10/2018 04:18:42
First Refresh Time : 09/10/2018 04:18:42
Last Refresh Time : 09/10/2018 04:22:40
Duration : 238s
Global Stats
==================================================================================================
| Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
==================================================================================================
| 339 | 76 | 262 | 0.16 | 0.06 | 3M | 33471 | 15GB | 2074 | 502MB |
==================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
====================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
====================================================================================================================================================================
| PX Coordinator | QC | | 3.39 | 2.39 | 0.80 | 0.16 | 0.04 | 212K | 55 | 440KB | | . | db file sequential read (1) |
| p006 | Set 1 | 1 | 11 | 3.50 | 7.70 | | 0.00 | | 382 | 93MB | 381 | 92MB | direct path read temp (4) |
| | | | | | | | | | | | | | direct path write temp (3) |
| p007 | Set 1 | 2 | 11 | 3.40 | 7.82 | | | | 382 | 93MB | 381 | 92MB | direct path read temp (4) |
| | | | | | | | | | | | | | direct path write temp (5) |
| p008 | Set 1 | 3 | 11 | 3.12 | 7.59 | | | | 382 | 93MB | 381 | 92MB | direct path read temp (5) |
| | | | | | | | | | | | | | direct path write temp (1) |
| p009 | Set 1 | 4 | 28 | 11 | 17 | | 0.01 | 122K | 2079 | 484MB | 558 | 135MB | db file scattered read (6) |
| | | | | | | | | | | | | | direct path read temp (4) |
| | | | | | | | | | | | | | direct path write temp (4) |
| p00a | Set 2 | 1 | 206 | 39 | 167 | | | 1M | 24921 | 9GB | 373 | 90MB | db file scattered read (69) |
| | | | | | | | | | | | | | db file sequential read (11) |
| | | | | | | | | | | | | | direct path read (69) |
| | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | direct path write temp (2) |
| p00b | Set 2 | 2 | 0.01 | | | | 0.01 | | | . | | . | |
| p00c | Set 2 | 3 | 68 | 13 | 55 | | | 703K | 5270 | 5GB | | . | direct path read (45) |
| p00d | Set 2 | 4 | 0.01 | 0.01 | | | 0.00 | | | . | | . | |
====================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4030684647)
=======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
=======================================================================================================================================================================================================================
| 0 | DELETE STATEMENT | | | | 2 | +237 | 1 | 0 | | | | | | | | |
| 1 | DELETE | STG_TAB1 | | | 4 | +235 | 1 | 0 | 55 | 440KB | | | | | 1.21 | Cpu (3) |
| | | | | | | | | | | | | | | | | db file sequential read (1) |
| 2 | PX COORDINATOR | | | | 2 | +237 | 9 | 17526 | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 3M | 816K | 3 | +235 | 4 | 17526 | | | | | | | | |
| 4 | HASH JOIN | | 3M | 816K | 123 | +116 | 4 | 17526 | 1528 | 370MB | 1524 | 369MB | 661M | 449M | 11.52 | Cpu (8) |
| | | | | | | | | | | | | | | | | direct path read temp (17) |
| | | | | | | | | | | | | | | | | direct path write temp (13) |
| 5 | PX RECEIVE | | 3M | 159K | 116 | +116 | 4 | 9M | | | | | | | 0.61 | Cpu (2) |
| 6 | PX SEND BROADCAST | :TQ10001 | 3M | 159K | 68 | +116 | 4 | 9M | | | | | | | 0.61 | Cpu (2) |
| 7 | PX SELECTOR | | | | 67 | +117 | 4 | 2M | | | | | | | | |
| 8 | TABLE ACCESS FULL | STG_TAB1 | 3M | 159K | 114 | +117 | 4 | 2M | 18406 | 3GB | | | | | 31.52 | Cpu (24) |
| | | | | | | | | | | | | | | | | db file scattered read (69) |
| | | | | | | | | | | | | | | | | db file sequential read (11) |
| 9 | VIEW | VW_NSO_1 | 3M | 657K | 3 | +235 | 4 | 17526 | | | | | | | | |
| 10 | HASH UNIQUE | | 3M | 657K | 7 | +231 | 4 | 17526 | | | | | 89M | | | |
| 11 | PX RECEIVE | | 3M | 657K | 1 | +231 | 4 | 17526 | | | | | | | | |
| 12 | PX SEND HASH | :TQ10002 | 3M | 657K | 116 | +115 | 4 | 17526 | | | | | | | | |
| 13 | HASH UNIQUE | | 3M | 657K | 116 | +115 | 4 | 17526 | | | | | 2M | | | |
| 14 | HASH JOIN SEMI | | 3M | 626K | 110 | +6 | 4 | 17526 | 374 | 91MB | 373 | 90MB | 169M | 104M | 6.67 | Cpu (19) |
| | | | | | | | | | | | | | | | | direct path read temp (1) |
| | | | | | | | | | | | | | | | | direct path write temp (2) |
| 15 | PX RECEIVE | | 3M | 34586 | 16 | +6 | 4 | 2M | | | | | | | | |
| 16 | PX SEND PARTITION (KEY) | :TQ10000 | 3M | 34586 | 9 | +6 | 4 | 2M | | | | | | | 0.91 | Cpu (3) |
| 17 | PX SELECTOR | | | | 9 | +6 | 4 | 2M | | | | | | | | |
| 18 | VIEW | index$_join$_003 | 3M | 34586 | 9 | +6 | 4 | 2M | | | | | | | | |
| 19 | HASH JOIN | | | | 13 | +2 | 4 | 2M | 177 | 43MB | 177 | 43MB | 134M | 55M | 0.91 | Cpu (3) |
| 20 | INDEX FAST FULL SCAN | STG_TAB1_IX2 | 3M | 10164 | 3 | +2 | 4 | 2M | 2 | 16384 | | | | | 0.30 | Cpu (1) |
| 21 | INDEX FAST FULL SCAN | STG_TAB1_IX3 | 3M | 21632 | 17 | +4 | 1 | 2M | 1518 | 349MB | | | | | 2.73 | Cpu (3) |
| | | | | | | | | | | | | | | | | db file scattered read (6) |
| 22 | PX PARTITION LIST ALL | | 48M | 591K | 95 | +21 | 2 | 32M | | | | | | | | |
| 23 | TABLE ACCESS FULL | TAB2 | 48M | 591K | 95 | +21 | 3 | 32M | 11411 | 11GB | | | | | 43.03 | Cpu (28) |
| | | | | | | | | | | | | | | | | direct path read (114) |
=======================================================================================================================================================================================================================