*************Below is the plan it used to take before***************************** Global Information ------------------------------ STATUS : DONE Instance ID : 3 SQL Execution ID : 50617726 Execution Started : 07/18/2021 14:15:23 First Refresh Time : 07/18/2021 14:15:26 Last Refresh Time : 07/18/2021 14:16:03 Duration : 40s Global Stats ============================================================================== | Elapsed | Cpu | IO | Cluster | PL/SQL | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | ============================================================================== | 44 | 24 | 19 | 0.66 | 1.37 | 778K | 51848 | 405MB | ============================================================================== SQL Plan Monitoring Details (Plan Hash Value=3120541595) ===================================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ===================================================================================================================================================================================================================== | 0 | INSERT STATEMENT | | | | 38 | +3 | 1 | 0 | | | | | | 1 | LOAD TABLE CONVENTIONAL | RTF | | | 38 | +3 | 1 | 0 | 83 | 664KB | 12.50 | Cpu (5) | | 2 | NESTED LOOPS | | 81 | 116K | 38 | +3 | 1 | 386K | | | | | | 3 | NESTED LOOPS | | 81 | 116K | 38 | +3 | 1 | 386K | | | | | | 4 | NESTED LOOPS | | 81 | 116K | 38 | +3 | 1 | 386K | | | | | | 5 | NESTED LOOPS | | 5558 | 110K | 38 | +3 | 1 | 7M | | | | | | 6 | TABLE ACCESS STORAGE FULL | FT | 4231 | 19 | 38 | +3 | 1 | 3976 | | | | | | 7 | PARTITION LIST ITERATOR | | 1 | 26 | 38 | +3 | 3976 | 7M | | | | | | 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFT | 1 | 26 | 40 | +1 | 3333 | 7M | 43942 | 343MB | 62.50 | Cpu (10) | | | | | | | | | | | | | | cell single block physical read (15) | | 9 | INDEX RANGE SCAN | FFT_IX7 | 818 | 4 | 38 | +3 | 3333 | 7M | 7823 | 61MB | 7.50 | Cpu (1) | | | | | | | | | | | | | | cell single block physical read (2) | | 10 | TABLE ACCESS BY INDEX ROWID BATCHED | RTNID | 1 | 2 | 38 | +3 | 7M | 386K | | | 2.50 | Cpu (1) | | 11 | INDEX RANGE SCAN | RTNID_IX6 | 1 | 1 | 38 | +3 | 7M | 386K | | | 12.50 | Cpu (5) | | 12 | INDEX UNIQUE SCAN | RDCUR_PK | 1 | | 38 | +3 | 386K | 386K | | | | | | 13 | TABLE ACCESS BY INDEX ROWID | RDCUR | 1 | 1 | 38 | +3 | 386K | 386K | | | 2.50 | Cpu (1) | ===================================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter(("FT"."ACK"="FFT"."CK" AND INTERNAL_FUNCTION("FFT"."STCD"))) 9 - access("FT"."FFXID"="FFT"."FXID") 10 - filter(("ND"."NE"='YY' OR "ND"."NE"='XX' OR "ND"."NE"='ZZ')) 11 - access("FFT"."CKEY"="ND"."NKEY") 12 - access("FFT"."CKEY"="CUR"."CKEY") *************Below is the plan it started opting post fundtion change***************************** Global Information ------------------------------ STATUS : EXECUTING Instance ID : 3 SQL Execution ID : 50617607 Execution Started : 07/18/2021 11:58:06 First Refresh Time : 07/18/2021 11:58:13 Last Refresh Time : 07/18/2021 13:40:03 Duration : 6117s Global Stats =========================================================================================== | Elapsed | Cpu | IO | Concurrency | Cluster | PL/SQL | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Gets | Reqs | Bytes | =========================================================================================== | 6126 | 5941 | 185 | 0.00 | 0.04 | 6.09 | 2G | 347K | 3GB | =========================================================================================== SQL Plan Monitoring Details (Plan Hash Value=4015732212) ========================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ========================================================================================================================================================================================================================= | 0 | INSERT STATEMENT | | | | 6101 | +7 | 1 | 0 | | | | | | 1 | LOAD TABLE CONVENTIONAL | RTF | | | 6101 | +7 | 1 | 0 | 54 | 432KB | 0.31 | Cpu (19) | | 2 | NESTED LOOPS | | 1 | 5005 | 6101 | +7 | 1 | 760K | | | 0.02 | Cpu (1) | | 3 | NESTED LOOPS | | 1 | 5005 | 6101 | +7 | 1 | 760K | | | 0.02 | Cpu (1) | | -> 4 | NESTED LOOPS | | 1 | 5004 | 6113 | +7 | 1 | 760K | | | 0.02 | Cpu (1) | | -> 5 | NESTED LOOPS | | 259 | 4932 | 6113 | +7 | 1 | 13M | | | | | | -> 6 | TABLE ACCESS STORAGE FULL | FT | 197 | 3 | 6113 | +7 | 1 | 4051 | | | | | | -> 7 | PARTITION LIST ITERATOR | | 1 | 25 | 6113 | +7 | 4052 | 13M | | | | | | -> 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFT | 1 | 25 | 6113 | +7 | 4048 | 13M | 295K | 2GB | 3.52 | Cpu (30) | | | | | | | | | | | | | | latch: gc element (1) | | | | | | | | | | | | | | cell multiblock physical read (1) | | | | | | | | | | | | | | cell single block physical read (182) | | -> 9 | INDEX RANGE SCAN | FFT_IX7 | 818 | 4 | 6113 | +7 | 4048 | 13M | 52161 | 408MB | 0.76 | Cpu (14) | | | | | | | | | | | | | | cell single block physical read (32) | | -> 10 | TABLE ACCESS STORAGE FULL | RTNID | 1 | | 6118 | +2 | 13M | 760K | | | 95.33 | Cpu (5793) | | -> 11 | INDEX UNIQUE SCAN | RDCUR_PK | 1 | | 6113 | +7 | 760K | 760K | | | | | | 12 | TABLE ACCESS BY INDEX ROWID | RDCUR | 1 | 1 | 6101 | +7 | 760K | 760K | | | 0.02 | Cpu (1) | ========================================================================================================================================================================================================================= Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter(("FT"."ACK"="FFT"."CK" AND INTERNAL_FUNCTION("FFT"."STCD"))) 9 - access("FT"."FFXID"="FFT"."FXID") 10 - storage(("FFT"."CKEY"="ND"."NKEY" AND INTERNAL_FUNCTION("ND"."NE"))) filter(("FFT"."CKEY"="ND"."NKEY" AND INTERNAL_FUNCTION("ND"."NE"))) 11 - access("FFT"."CKEY"="CUR"."CKEY")