Hi, we are using version -11.2.0.4 of oracle. We have a complex INSERT query running for ~30 minutes+ for inserting ~30million records(INSERT INTO tab1.. SELECT..), mainly the SELECT query part is complex one(~1000 lines of code). We actually have multiple bitmap index present in the table, so before running this data load we mark the index UNUSABLE and then execute the INSERT. Now during one of the execution from realtime monitoring i see two section in the execution plan and want to ensure those are not the bottleneck in the current data load query. I have simplified the query and replicated on DEV ,so that to only focus on INSeRT part of the query for time being rather SELECT part.
1)In plan line-id - 3 , Why do we see "INDEX MAINTENANCE" in the execution path even if the indexes are marked UNUSABLE. Is this expected behavior or we are doing anything wrong?
2)We have one sequence which is defined as below, so during presence of this sequence the data load takes additional ~10% time. Is this expected or we can make this better considering batch insert.
insert /*+APPEND parallel(4)*/into tab1(c1,c2,c3...) select /*+parallel(4)*/ c1,c2,c3... from tab1;
sql monitor with no sequence involved:
----------------------------
Global Information
------------------------------
Status : DONE
Execution Started : 01/09/2018 09:00:21
First Refresh Time : 01/09/2018 09:00:22
Last Refresh Time : 01/09/2018 09:01:24
Duration : 63s
Module/Action : SQL*Plus/-
Global Stats
=====================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
=====================================================================================================================================
| 137 | 121 | 14 | 0.00 | 0.80 | 0.04 | 0.24 | 2M | 14004 | 8GB | 30918 | 8GB | -96.08% |
=====================================================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
====================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #) |
====================================================================================================================================================================================================
| PX Coordinator | QC | | 0.83 | 0.01 | | 0.00 | 0.75 | | 0.07 | 82 | | . | | . | NaN% | os thread startup (1) |
| p000 | Set 1 | 1 | 8.44 | 7.76 | 0.69 | | | | | 237K | 3370 | 2GB | | . | 9.91% | cell smart table scan (2) |
| p001 | Set 1 | 2 | 8.86 | 8.08 | 0.78 | | | | | 248K | 3520 | 2GB | | . | 9.91% | |
| p002 | Set 1 | 3 | 8.69 | 7.98 | 0.71 | | | | | 248K | 3514 | 2GB | | . | 9.91% | cell smart table scan (2) |
| p003 | Set 1 | 4 | 8.99 | 8.26 | 0.72 | | | | | 254K | 3600 | 2GB | | . | 9.91% | cell smart table scan (1) |
| p004 | Set 2 | 1 | 12 | 10 | 1.11 | | 0.01 | 0.01 | 0.02 | 117K | | . | 3552 | 884MB | -203.03% | direct path write (1) |
| p005 | Set 2 | 2 | 13 | 12 | 1.68 | | 0.01 | 0.01 | 0.06 | 129K | | . | 3913 | 1GB | -203.03% | direct path write (1) |
| p006 | Set 2 | 3 | 14 | 13 | 1.78 | | 0.01 | 0.01 | 0.00 | 139K | | . | 4244 | 1GB | -203.03% | direct path write (6) |
| p007 | Set 2 | 4 | 62 | 54 | 6.96 | | 0.01 | 0.01 | 0.08 | 619K | | . | 19209 | 5GB | -203.03% | direct path write (9) |
====================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2293215455)
===================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
===================================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +63 | 9 | 10 | | | | | | | | |
| 1 | PX COORDINATOR | | | | 63 | +1 | 9 | 10 | | | | | | | 0.72 | os thread startup (1) |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 2 | 1 | +63 | 4 | 10 | | | | | | | | |
| 3 | INDEX MAINTENANCE | TAB1 | | | 1 | +63 | 4 | 10 | | | | | | | | |
| 4 | PX RECEIVE | | 1 | 2 | 1 | +63 | 4 | 10 | | | | | | | | |
| 5 | PX SEND RANGE | :TQ10001 | 1 | 2 | 1 | +63 | 4 | 10 | | | | | | | | |
| 6 | LOAD AS SELECT | | | | 62 | +2 | 4 | 14 | | | 30918 | 8GB | | 4M | 63.04 | Cpu (70) |
| | | | | | | | | | | | | | | | | direct path write (17) |
| 7 | PX RECEIVE | | 1 | 2 | 61 | +3 | 4 | 30M | | | | | | | 11.59 | Cpu (16) |
| 8 | PX SEND PARTITION (KEY) | :TQ10000 | 1 | 2 | 62 | +2 | 4 | 30M | | | | | | | 15.22 | Cpu (21) |
| 9 | PX BLOCK ITERATOR | | 1 | 2 | 61 | +3 | 4 | 30M | | | | | | | | |
| 10 | TABLE ACCESS STORAGE FULL | TAB1 | 1 | 2 | 61 | +3 | 64 | 30M | 14004 | 8GB | | | 9.91% | | 9.42 | Cpu (8) |
| | | | | | | | | | | | | | | | | cell smart table scan (5) |
===================================================================================================================================================================================================================
insert /*+APPEND parallel(4)*/into tab1(c1,c2,c3...) select /*+parallel(4)*/ c1,c2,seq1.nextval... from tab1;
sql monitor in presence of sequence:(plan line_id-9)
----------------------------------------------------
Global Information
------------------------------
Status : DONE
Execution Started : 01/09/2018 09:03:21
First Refresh Time : 01/09/2018 09:03:21
Last Refresh Time : 01/09/2018 09:04:31
Duration : 70s
Global Stats
==========================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
==========================================================================================================================
| 154 | 141 | 12 | 0.10 | 0.04 | 0.23 | 2M | 13599 | 8GB | 31068 | 8GB | -92.31% |
==========================================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
=========================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Write | Write | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #) |
=========================================================================================================================================================================================
| PX Coordinator | QC | | 0.21 | 0.01 | | 0.10 | | 0.10 | 82 | | . | | . | NaN% | |
| p000 | Set 1 | 1 | 9.48 | 9.08 | 0.38 | | 0.01 | | 230K | 3187 | 2GB | | . | 12.28% | enq: SQ - contention (1) |
| | | | | | | | | | | | | | | | |
| p001 | Set 1 | 2 | 10 | 9.33 | 0.45 | | 0.01 | | 236K | 3221 | 2GB | | . | 12.28% | enq: SQ - contention (1) |
| | | | | | | | | | | | | | | | |
| p002 | Set 1 | 3 | 11 | 10 | 0.47 | | 0.01 | | 270K | 3704 | 2GB | | . | 12.28% | cell smart table scan (1) |
| p003 | Set 1 | 4 | 10 | 10 | 0.44 | | 0.01 | | 253K | 3487 | 2GB | | . | 12.28% | cell smart table scan (1) |
| p004 | Set 2 | 1 | 13 | 12 | 1.43 | | | 0.02 | 115K | | . | 3580 | 891MB | -203.03% | direct path write (1) |
| p005 | Set 2 | 2 | 14 | 13 | 1.24 | | | 0.01 | 126K | | . | 3945 | 1GB | -203.03% | direct path write (2) |
| p006 | Set 2 | 3 | 16 | 14 | 1.71 | | | | 137K | | . | 4292 | 1GB | -203.03% | direct path write (1) |
| p007 | Set 2 | 4 | 69 | 63 | 6.16 | | 0.00 | 0.10 | 616K | | . | 19251 | 5GB | -203.03% | direct path write (6) |
=========================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3359545178)
====================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
====================================================================================================================================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +70 | 9 | 10 | | | | | | | | |
| 1 | PX COORDINATOR | | | | 1 | +70 | 9 | 10 | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 2 | 1 | +70 | 4 | 10 | | | | | | | | |
| 3 | INDEX MAINTENANCE | TAB1 | | | 1 | +70 | 4 | 10 | | | | | | | | |
| 4 | PX RECEIVE | | 1 | 2 | 1 | +70 | 4 | 10 | | | | | | | | |
| 5 | PX SEND RANGE | :TQ10001 | 1 | 2 | 1 | +70 | 4 | 10 | | | | | | | | |
| 6 | LOAD AS SELECT | | | | 70 | +1 | 4 | 14 | | | 31068 | 8GB | | 4M | 66.44 | Cpu (89) |
| | | | | | | | | | | | | | | | | direct path write (10) |
| 7 | PX RECEIVE | | 1 | 2 | 69 | +2 | 4 | 30M | | | | | | | 10.07 | Cpu (15) |
| 8 | PX SEND PARTITION (KEY) | :TQ10000 | 1 | 2 | 70 | +1 | 4 | 30M | | | | | | | 11.41 | Cpu (17) |
| 9 | SEQUENCE | SEQ1 | | | 70 | +1 | 4 | 30M | | | | | | | 4.70 | enq: SQ - contention (2) |
| | | | | | | | | | | | | | | | | Cpu (5) |
| 10 | PX BLOCK ITERATOR | | 1 | 2 | 69 | +2 | 4 | 30M | | | | | | | | |
| 11 | TABLE ACCESS STORAGE FULL | TAB1 | 1 | 2 | 69 | +2 | 64 | 30M | 13599 | 8GB | | | 12.28% | | 6.71 | Cpu (8) |
| | | | | | | | | | | | | | | | | cell smart table scan (2) |
====================================================================================================================================================================================================================