Re: Cause behind execution plan change

Thank you very much. I was not aware of the same, all these years, I must have seen the baseline information while plan output was fetched from memory i.e. using dbms_xplan.display_cursor but not from AWR i.e. using dbms_xplan.display_awr. I almost missed this key difference. Thanks again.

To fix this issue as you and Jonathan mentioned, So I think we should try to use hints to fix this sql on a permanent basis as it's now clear there was the baseline associated in the past and got detached now because of code change.

For my knowledge, I never used sql patch , so i have few doubts 1) Is there some benefit of it over sql profile(which we create using dbms_sqltune.import_sql_profile and feed hints/outlines to a sql) or sql plan baseline and if any additional privilege required for creating a sql patch?
2)If this sql patch information will also be visible in dbms_xplan.display_awr?
3)Who will get priority if a sql has all three i.e. sql profile, baseline, sql patch?
4)Is there a force_match option in sql patch like we have it in while creating a sql profile to cater different sql_ids using the same sql text but just differ in literals?
5) Will sql patch just override the existing hints in the sql or will it get appended to the existing hints whatever is already there in that sql?

Regards
Pap

On Wed, Jul 21, 2021 at 11:45 AM Laurentiu Oprea < laurentiu.oprea06_at_gmail.com> wrote:

> 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