INSERT INTO SFE (..................) SELECT SFE_SEQ.NEXTVAL,.................. FROM MFE , SCD WHERE MFE.FID IS NULL AND MFE.PCODE = SCD.PCODE AND SCD.PIND = 0 AND MFE.PCODE NOT IN ('XXX', 'YYY', 'ZZZ',..... 23 values) AND MFE.STAT = 'AA' AND MFE.IN_DT <= :B1 AND MFE.MA_CD IN ('AA', 'BB') AND MFE.FID IS NULL ********************* Good Plan with which it executes most of the time**************** Global Information ------------------------------ Status : DONE Instance ID : 1 SQL Execution ID : 16777222 Execution Started : 03/19/2021 22:13:18 First Refresh Time : 03/19/2021 22:13:22 Last Refresh Time : 03/19/2021 22:14:02 Duration : 44s PLSQL Entry Ids (Object/Subprogram) : 99136,10 PLSQL Current Ids (Object/Subprogram) : 99136,10 Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :B1 | 2 | DATE | 03/19/2021 00:00:00 | ======================================================================================================================== Global Stats ===================================================================================================================== | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload | ===================================================================================================================== | 44 | 42 | 0.71 | 0.08 | 0.03 | 0.23 | 0.23 | 3M | 17029 | 16GB | 98.99% | ===================================================================================================================== SQL Plan Monitoring Details (Plan Hash Value=1647532331) ============================================================================================================================================================================================ | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | ============================================================================================================================================================================================ | 0 | INSERT STATEMENT | | | | 41 | +4 | 1 | 0 | | | | | | | | 1 | LOAD TABLE CONVENTIONAL | SFE | | | 43 | +2 | 1 | 0 | 5 | 40960 | | | | | | 2 | SEQUENCE | SFE_SEQ | | | 42 | +3 | 1 | 3M | | | | | | | | 3 | HASH JOIN | | 543K | 906K | 41 | +4 | 1 | 3M | | | | 2M | | | | 4 | INDEX STORAGE FAST FULL SCAN | SCD_IX1 | 1060 | 4 | 1 | +4 | 1 | 1042 | | | | | | | | 5 | PARTITION LIST ALL | | 543K | 906K | 41 | +4 | 1 | 3M | | | | | | | | 6 | PARTITION RANGE ALL | | 543K | 906K | 41 | +4 | 2 | 3M | | | | | | | | 7 | TABLE ACCESS STORAGE FULL | MFE | 543K | 906K | 41 | +4 | 138 | 3M | 17013 | 16GB | 98.99% | 15M | | | ============================================================================================================================================================================================ Plan hash value: 1647532331 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 341K(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | 2 | SEQUENCE | SFE_SEQ | | | | | | | | 3 | HASH JOIN | | 366K| 27M| 341K (1)| 00:34:08 | | | | 4 | INDEX STORAGE FAST FULL SCAN| SCD_IX1 | 783 | 7047 | 3 (0)| 00:00:01 | | | | 5 | PARTITION LIST ALL | | 366K| 24M| 341K (1)| 00:34:08 | 1 | 2 | | 6 | PARTITION RANGE ALL | | 366K| 24M| 341K (1)| 00:34:08 | 1 | 57 | | 7 | TABLE ACCESS STORAGE FULL | MFE | 366K| 24M| 341K (1)| 00:34:08 | 1 | 114 | --------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 4 - SEL$1 / SCD@SEL$1 7 - SEL$1 / MFE@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INS$1") FULL(@"INS$1" "SFE"@"INS$1") INDEX_FFS(@"SEL$1" "SCD"@"SEL$1" ("SCD"."PIND" "SCD"."PCODE")) FULL(@"SEL$1" "MFE"@"SEL$1") LEADING(@"SEL$1" "SCD"@"SEL$1" "MFE"@"SEL$1") USE_HASH(@"SEL$1" "MFE"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 2 - :B1 (DATE, Primary=1) ****************************************** BAD Plan happened twice ********************************* Global Information ------------------------------ Status : DONE Instance ID : 1 SQL Execution ID : 16777246 Execution Started : 04/12/2021 22:13:03 First Refresh Time : 04/12/2021 22:13:07 Last Refresh Time : 04/12/2021 22:28:52 Duration : 949s PLSQL Entry Ids (Object/Subprogram) : 99136,10 PLSQL Current Ids (Object/Subprogram) : 99136,10 Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :B1 | 2 | DATE | 04/12/2021 00:00:00 | ======================================================================================================================== Global Stats ================================================================================= | Elapsed | Cpu | IO | Concurrency | Cluster | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | ================================================================================= | 1071 | 251 | 800 | 0.03 | 20 | 3M | 2M | 14GB | ================================================================================= SQL Plan Monitoring Details (Plan Hash Value=4235768614) ========================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ========================================================================================================================================================================================== | 0 | INSERT STATEMENT | | | | 946 | +4 | 1 | 0 | | | | | | | 1 | LOAD TABLE CONVENTIONAL | SFE | | | 946 | +4 | 1 | 0 | 40 | 320KB | | | | | 2 | SEQUENCE | SFE_SEQ | | | 946 | +4 | 1 | 2M | | | | | | | 3 | HASH JOIN | | 228K | 708K | 946 | +4 | 1 | 2M | | | 2M | | | | 4 | INDEX STORAGE FAST FULL SCAN | SCD_IX1 | 1063 | 4 | 1 | +4 | 1 | 1045 | | | | | | | 5 | TABLE ACCESS BY GLOBAL INDEX ROWID | MFE | 228K | 708K | 949 | +1 | 1 | 2M | 1M | 10GB | | | | | 6 | INDEX RANGE SCAN | MFE_IX4 | 16M | 124K | 947 | +4 | 1 | 49M | 428K | 3GB | | | | ========================================================================================================================================================================================== Plan hash value: 4235768614 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | | | 603K(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | SFE | | | | | | | | 2 | SEQUENCE | SFE_SEQ | | | | | | | | 3 | HASH JOIN | | 436K| 33M| 603K (1)| 00:00:24 | | | | 4 | INDEX STORAGE FAST FULL SCAN | SCD_IX1 | 1038 | 9342 | 4 (0)| 00:00:01 | | | | 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| MFE | 436K| 29M| 603K (1)| 00:00:24 | ROWID | ROWID | | 6 | INDEX RANGE SCAN | MFE_IX4 | 14M| | 105K (1)| 00:00:05 | | | ------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 4 - SEL$1 / SCD@SEL$1 5 - SEL$1 / MFE@SEL$1 6 - SEL$1 / MFE@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('12.1.0.2') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INS$1") FULL(@"INS$1" "SFE"@"INS$1") INDEX_FFS(@"SEL$1" "SCD"@"SEL$1" ("SCD"."PIND" "SCD"."PCODE")) INDEX_RS_ASC(@"SEL$1" "MFE"@"SEL$1" ("MFE"."STAT" "MFE"."MA_CD")) NUM_INDEX_KEYS(@"SEL$1" "MFE"@"SEL$1" "MFE_IX4" 1) LEADING(@"SEL$1" "SCD"@"SEL$1" "MFE"@"SEL$1") USE_HASH(@"SEL$1" "MFE"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 2 - :B1 (DATE, Primary=1) Note ----- - PDML disabled because object is not decorated with parallel clause - Direct Load disabled because no append hint given and not executing in parallel