INSERT INTO RTF(...) SELECT /*+ ordered use_nl(ft FFT nd curr)*/ ND.NE, ND.NID, CUR.SCD, FT.FXID, FT.TFXID, fun1 (FFT.AMT, FT.STS, FT.PDT, :B3, TRUNC ( :B2), 'S'), fun1 (FFT.AMT, FT.STS, FT.PDT, :B3, TRUNC ( :B2), 'F'), TRUNC ( :B1), ND.MCID FROM FT , FFT , RTNID ND, RDCUR CUR WHERE FT.FFXID = FFT.FXID AND FT.ACK = FFT.CK AND FFT.CKEY = ND.NKEY AND ND.NE IN ('XX', 'YY', 'ZZ') AND FFT.STCD IN ('X', 'Y') AND FFT.CKEY = CUR.CKEY *************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") Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_aggr_groupby_elim' 'false') OPT_PARAM('_optimizer_reduce_groupby_key' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INS$1") FULL(@"INS$1" "RTF"@"INS$1") FULL(@"SEL$1" "FT"@"SEL$1") INDEX_RS_ASC(@"SEL$1" "FFT"@"SEL$1" ("FFT"."FXID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "FFT"@"SEL$1") INDEX_RS_ASC(@"SEL$1" "ND"@"SEL$1" ("ND"."NKEY")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "ND"@"SEL$1") INDEX(@"SEL$1" "CUR"@"SEL$1" ("CUR"."CKEY")) LEADING(@"SEL$1" "FT"@"SEL$1" "FFT"@"SEL$1" "ND"@"SEL$1" "CUR"@"SEL$1") USE_NL(@"SEL$1" "FFT"@"SEL$1") USE_NL(@"SEL$1" "ND"@"SEL$1") USE_NL(@"SEL$1" "CUR"@"SEL$1") NLJ_BATCHING(@"SEL$1" "CUR"@"SEL$1") END_OUTLINE_DATA */ Note ----- - dynamic statistics used: dynamic sampling (level=2) *************Below is the plan it started opting post function 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") Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_gather_feedback' 'false') OPT_PARAM('_optimizer_aggr_groupby_elim' 'false') OPT_PARAM('_optimizer_reduce_groupby_key' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"INS$1") FULL(@"INS$1" "RTF"@"INS$1") FULL(@"SEL$1" "FT"@"SEL$1") INDEX_RS_ASC(@"SEL$1" "FFT"@"SEL$1" ("FFT"."FXID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "FFT"@"SEL$1") FULL(@"SEL$1" "ND"@"SEL$1") INDEX(@"SEL$1" "CUR"@"SEL$1" ("CUR"."CKEY")) LEADING(@"SEL$1" "FT"@"SEL$1" "FFT"@"SEL$1" "ND"@"SEL$1" "CUR"@"SEL$1") USE_NL(@"SEL$1" "FFT"@"SEL$1") USE_NL(@"SEL$1" "ND"@"SEL$1") USE_NL(@"SEL$1" "CUR"@"SEL$1") NLJ_BATCHING(@"SEL$1" "CUR"@"SEL$1") END_OUTLINE_DATA */ Note ----- - dynamic statistics used: dynamic sampling (level=2)