Re: Cause behind execution plan change

The note part will not show you if a baseline was used, will show you only if a SQL patch was used ( and I think a SQL profile).

Based on your comments most probably you had a baseline in place.

Some solutions can be:

1. Review the hints inside your code, consider Jonathan's comments, also consider to hint the index usage as well.
2. You can use a SQL patch to fix current problem to attach the index hint

> Thank You Jonathan and Lok. Attaching again the query along with the
> outline and note section.
>
> I am seeing one usage of INTERNAL_FUNCTION around FFT.STCD but in the new
> plan(post function change), I am seeing two more usage of INTERNAL_FUNCTION
> around the ND.NE column. These columns are the same with respect to the
> data type in both sides of the predicate, why are these appearing and if
> anyway these are responsible for some wrong estimation?
>
> I had checked the dba_hist_sqlstat but didn't see any profiles attached
> for the old sql and als checked the plan from display_awr and the note
> section was only showing below i.e. usage of dynamic sampling only and
> nothing regarding sql profile or plan baselines either. But then when I
> query dba_sql_plan_baselines manually with the sql_text like '%...sample
> query text...%', I saw one entry there with ACCEPTED and ENABLED both
> columns set as 'YES'. And also the signature is matching with the query
> force_matching_signature. And I can see the last_executed column was also
> showing the date close to when we introduced the new modified sql into
> prod. So it seems this was the one getting used for old sql/query but the
> note section does not state that.
>
> So is it true that it may be possible that the note section of the
> display_awr function won't show the usage of profile/baseline but still it
> may be used by that query internally?
>
> Note
> -----
> - dynamic statistics used: dynamic sampling (level=2)
>
> On Mon, Jul 19, 2021 at 12:47 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> You shouldn't be using the ORDERED hint, by the way, you should learn how
>> to use the LEADING() hint.
>> And since you've dictated the join order for this query FT does not need
>> to be in the USE_NL() hint because it's the first table in the join order
>> so it's not going to appear as the second table in any of the joins. (See:
>> https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/ , and for
>> the equivalent comment on the use_hash() hint see:
>> https://jonathanlewis.wordpress.com/2013/09/07/hash-joins/ )
>>
>>
>> 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