Re: Cause behind execution plan change

Your first move should be to look at all the available information - this includes the Outline Information and the Notes from the execution plan. In the absence of complete information the first obvious guess would be that there's an SQL_Patch (or SQL_Profile, or SQL_Plan_Baseline) attached to the original query which isn't attached to the newer version because the SQL_ID has changed.

Regards
Jonathan Lewis

On Sun, 18 Jul 2021 at 20:35, Pap <oracle.developer35_at_gmail.com> wrote:

> 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
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l