Re: Cause behind execution plan change

I read or experienced in the past that the user defined plsql function will cause a ~1%(or 25%) estimation guess for the predicate. But as in your query the function was already used in the past but not as a predicate and that to you have just changed/added a new input parameter to the call, so ideally this should not impact the estimation/cost.

And as you rightly said it looks like the estimation of global temporary table FT is causing the difference. So do you have any changes done with respect to data volume etc, which can impact this estimation?

On Mon, Jul 19, 2021 at 1:05 AM 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