Re: Cause behind execution plan change

I don't have access to my computer to give you a proper answer:

As far as I remember having an SQL patch and a SQL profile will not work, trying to create one when the other exist will give you an error.

You are able to use a baseline together with a SQL patch, I'm doing this often when I want to change the level of dynamic sampling or parallel level.

SQL patch is targeted to a particular sqlid. Will not overwrite the embedded hints unless you add IGNORE_OPTIM_EMBEDDED_HINTS.

declare

   v_sql CLOB;
begin

   select sql_text into v_sql from dba_hist_sqltext where sql_id='&sqlid';    sys.dbms_sqldiag_internal.i_create_patch(

      sql_text  => v_sql,
      hint_text => 'INDEX_RS_ASC(_at_"SEL$1" "ND"_at_"SEL$1" ("ND"."NKEY"))',
      name      => 'my_patch');

You can use something like this to force the index you need.(again because I'm using the phone I might be wrong with the index )

On Wed, Jul 21, 2021, 15:02 Pap <oracle.developer35_at_gmail.com> wrote:

> 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