Hello listers, It's version 12.1.0.2.0 of oracle. We have done a change to
the code inside the function which gets called from the SELECT query. But
as its just been used in the SELECT part of the query ideally it should not
change sql_id of the query and also the plan, but we also add one new
additional input parameter(i.e. :B3 below) to the function call and thus
sql_id got changed which is understood. But something which we are not able
to understand is , why did the plan change occurred after this change?
Attached is both the plans i.e the one it used to take in the past vs the
current one which it's now taking. From the plan it does look like , its
cardinality estimation of global temporary table FT which causes the
difference, as it puts table RTNID in index access vs FTS access in a
nested loop. But the old query(before function change) was not taking the
bad plan ever, but it started taking after function change. So wondering
how a new input parameter addition to a function which is not part of the
WHERE clause, can cause this sort of impact and how to fix it?
In this query, all the tables are global temporary tables except FFT, which
is a list partition table with partition key as CKEY.
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")
*************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")
