Re: Cause behind execution plan change

https://orastory.wordpress.com/2014/02/05/awr-was-a-baselined-plan-used/

On Tue, Jul 20, 2021, 21:59 Lok P <loknath.73_at_gmail.com> wrote:

> *"The note part will not show you if a baseline was used" *
>
> I have seen usage of both "plan baseline" and "sql profile" in the NOTE
> section of the AWR plan. If it's not showing up in one odd case can this be
> related to some bug? Others can correct this point.
>
> On Wed, Jul 21, 2021 at 12:51 AM Laurentiu Oprea <
> laurentiu.oprea06_at_gmail.com> wrote:
>
>> 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
>>
>> Thanks.
>>
>> On Tue, Jul 20, 2021, 14:37 Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> 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