Re: Priority of profile baseline patch

If you see the outlines which you posted, your old plan/good plan was also having newly 12.2. introduced or_expand used but was mostly doing for two predicates/sets whereas the new plan/bad plan is applying it on three different sets. So ideally it should have been able to produce the old plan.

As Jonathan already mentioned you mostly have to get the 10053 trace and search for the section which is showing the failure of the existing baseline usage.

Old plan outline:-
OR_EXPAND(_at_"SET$CD5DAC4C_1" (1) (2))

New plan outline:-
OR_EXPAND(_at_"SET$CD5DAC4C_1" (1) (2) (3))

On Tue, Dec 21, 2021 at 1:49 AM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank You So much Jonathan.
>
> I think you are spot on wrt the OR expansion point. The plan which I am
> seeing now is not getting reproduced and thus causing a new
> baseline generation is having OR expansion. And thus the total number of
> lines in this new plan is ~113 as compared to ~87 in the earlier plan. I
> have attached the sample query with the old plan and the new one with their
> respective outlines.
>
> But yes the question would be why optimizer is now not able to produce the
> old plan(i.e mostly without OR expansion). No such object definition change
> or parameter setup changes have been done. Statistics are getting gathered
> on a daily basis on the underlying objects but that should not cause such
> issues. And this plan suddenly appeared after a particular date and is
> continuing now. And the note section in the new plan is showing the section
> below (i.e. it failed to reproduce the baseline). And used the sql profile,
> but the sql profile plan/old plan is not exactly the same as this one.
>
> We have not had any sql patch created here but yes the sql profile was not
> created using the traditional method(which would have a bunch of
> opt_estimate hints) rather its created by forcing the exact outline hints
> of an awr plan i.e the old plan in the attached doc. So do you think that
> forced outline hints through sql profile can cause such an issue?
>
> Note
>
> -----
>
> - dynamic statistics used: statistics for conventional DML
>
> - SQL profile "XXXXXX" used for this statement
>
> - Failed to use SQL plan baseline for this statement
>
> On Sun, Dec 19, 2021 at 4:23 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> It's important to remember that SQL Plan Baselines are (still) not
>> perfect. I have an example in 19.11 where I've created an SQL Plan
>> Baseline using an official methods, and the execution plan will not
>> reproduce when the baseline is activated. (See
>> https://jonathanlewis.wordpress.com/2020/02/24/fake-baselines-2/ )
>>
>> Oracle has been enhancing hints over the last few years to try an
>> eliminate the risk of a hint allowing more than one option to be used - for
>> example the merge hint has been extended to allow for merge( {queryblock1}
>> > {queryblock2} ) because it was possble for the optimizer to see a
>> non-parameterised merge hint in a query block and merge it into the wrong
>> query block - hence obeying the hint but doing the wrong thing.
>>
>> It's hard to see what's gone wrong when looking at baselines because the
>> baseline tends to describe the mechanics of how the plan should operate
>> without saying anything about how the optimizer got to a position where
>> those mechanics could work (e.g. you might see hints saying use_nl(tabX)
>> index( tabX(col3,col7, col9)) without any clue that a nested loop into that
>> table became possible because the optimizer decided that it could unnest a
>> subquery in a certaini way and they do complex view merging). There's a
>> little more about this, and some suggestions about a potentially helpful
>> strategy in a note I wrote about the query block registry option in 19c at
>> this URL: https://jonathanlewis.wordpress.com/2021/08/24/qbregistry/
>>
>>
>> You haven't given us much of a clue about why you might be having a
>> problem, but based on the large number of unresolved hints, and the query
>> block names in the few hints you've supplied, you've almost certainly got a
>> query where the optimizer has done OR Expansion (rather than the
>> Concatenation of earlier versions), so you might be in a fairly new code
>> path where Oracle still needs to refine some use of hints in baselines.
>>
>> As usual the trigger for a change in plan is a change in the results of
>> cardinality calculations - which means thinking about the statistics, and
>> thinking about bind values used during optimisation and possible effects of
>> histograms. Since you've also created an SQL Profile you also have to
>> allow for the fact that the statistical hints it supplies are no longer
>> relevant and are potentially a threat (which is why I think it's a bad idea
>> to have both a profile and a baseline for the same query). Finding the
>> cause of the change isn't sufficient, however; you (or Oracle support) need
>> to discover the gap in hinting that results in a baseline failing to
>> reproduce the plan when NOTHING HAS CHANGED. (Critically, I am assuming
>> that no-one has done anything like changing index definitions, messing
>> around with character sets or NLS settings, or optimizer settings; and I'm
>> also assuming that the profile you've got is a genuine profile created by
>> running dbms_sqltune and isn't a hack that includes hints that shouldn't be
>> there; I'm also assuming that any SQL_patch you've attached to the query
>> hasn't had a side effect that blocks reproduction of the plan.)
>>
>>
>> The initial steps you need to take are: check for details that might make
>> the accepted baseline fail to reproduce the plan. As a starting point you
>> could run the 10053 trace and search for lines starting "SPM:" to check for
>> a section saying: "SPM: failed to reproduce the plan using the following
>> info:" If necessary you could enable the CBO trace system-wide for this SQL
>> statement so that you find the trace file for every time the statement was
>> optimised, and this might give you a clue why (or if) the plan was accepted
>> on some occasions but not on others. Another check would be to see if any
>> inpu bind values could produce large variations in cardinality estimates.
>> You could also check if there are any hints in the accepted plan that could
>> allow for variations in interpretation; at the same time you could also
>> check the hints for the used bad baseline to see if there are any hints
>> there that could have needed a blocking hint in the good plan.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Sat, 18 Dec 2021 at 20:52, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Thank you very much Mohamed.
>>>
>>> In one of our cases, we have a complex sql with some embedded hints and
>>> on top of that, we have a sql profile created. And again it was somehow
>>> going for a bad execution path so someone created a baseline on another
>>> path from the available awr plan. And now the optimizer is going for a new
>>> sql baseline altogether with the accepted flag as 'NO'.
>>>
>>> And while checking the details of the plan we are seeing the "hints
>>> report" section holding some ~44 unresolved hints which were not there in
>>> case of sql profile or the baseline which is having flag accepted = YES.
>>> And then I saw the query block that unresolved hints pointing to (i.e. SEL$0F3929D0)
>>> was there in the outline section of the sql profile which was initially
>>> created but it's no longer there in the new sql plan baseline(with accepted
>>> flag-No). So as we have not changed the sql text (and also that would
>>> have caused it to change the sql_id itself). So what can cause such a
>>> change in the query block name impacting existing paths?
>>>
>>> Note:- The old sql profile and the sql plan baseline(the one with
>>> accepted flag=yes) were not showing any Unresolved hints report section.
>>> But this new sql plan baseline is showing ~44 unresolved hints sections.
>>>
>>> N - Unresolved (44))
>>> 0 - SEL$0F3929D0
>>>
>>> N - FULL(_at_"SEL$0F3929D0" "MDD"_at_"SET$9A7C2438_1")
>>>
>>> N - FULL(_at_"SEL$0F3929D0" "MTD"_at_"SET$9A7C2438_1")
>>>
>>> N - LEADING(_at_"SEL$0F3929D0" "MDT"_at_"SET$9A7C2438_1"
>>> "MDD"_at_"SET$9A7C2438_1" "MREF"_at_"SET$9A7C2438_1" "MTD"@"SET$9A7C2438_1")
>>>
>>> N - PX_JOIN_FILTER(_at_"SEL$0F3929D0" "MTD"_at_"SET$9A7C2438_1")
>>>
>>> N - SWAP_JOIN_INPUTS(_at_"SEL$0F3929D0" "MREF"_at_"SET$9A7C2438_1")
>>>
>>> N - USE_HASH(_at_"SEL$0F3929D0" "MTD"_at_"SET$9A7C2438_1")
>>>
>>>
>>>
>>> On Fri, Dec 17, 2021 at 9:32 PM Mohamed Houri <mohamed.houri_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>>
>>>> *So wanted to understand in what situation the existing baseline
>>>> accepted+enabled plan can be non-reproducible?*
>>>>
>>>> Here’s an answer to your question hoping that it is still valid more
>>>> than 7 years later
>>>>
>>>>
>>>> https://hourim.wordpress.com/2014/03/17/spm-reproducibility-changing-the-_optimizer_skip_scan_enabled-value/
>>>>
>>>> Best regards
>>>>
>>>> Mohamed Houri
>>>>
>>>> Le ven. 17 déc. 2021 à 16:48, Pap <oracle.developer35_at_gmail.com> a
>>>> écrit :
>>>>
>>>>> Thank You So much for the clarification. It really helped me
>>>>> understand the flow.
>>>>>
>>>>> *"with enabled=yes but accepted=NO and*
>>>>>
>>>>> *1) All the accepted and enabled SQL plan baselines will be
>>>>> reproduced and costed. The best costed and reproduced plan will be used*
>>>>>
>>>>> *2) If none of the accepted and enabled SQL plan baselines
>>>>> is reproducible then the CBO plan added to the SQL plan baseline list with
>>>>> enabled=yes but accepted=NO will be used"*
>>>>>
>>>>> Related to the above point ,I am wondering about the fact that , for
>>>>> the first time, when the sql plan baseline was created , it must have
>>>>> been created on top of one of the available awr plans or cursor cache
>>>>> plans, which the query must have taken in the past execution. So in what
>>>>> situation that same plan won't get reproducible, such that the optimizer
>>>>> has to go for a non accepted plan i.e the second option above which can be
>>>>> riskier one?
>>>>>
>>>>> Actually it's matching to one situation, we had encountered a
>>>>> scenario in the past in which we had hints used in a query and on top of
>>>>> that, that sql was having a profile created using the outline of a awr
>>>>> plan. And again it was somehow going for a bad execution path so someone
>>>>> created a baseline on another path from the available awr plan. And this
>>>>> caused the optimizer to go for a different plan altogether, breaking one of
>>>>> the functionality. So wanted to understand in what situation the existing
>>>>> baseline accepted+enabled plan can be non reproducible?
>>>>>
>>>>>
>>>>>
>>>>> On Fri, Dec 17, 2021 at 8:31 PM Mohamed Houri <mohamed.houri_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Pap,
>>>>>>
>>>>>> *As you mentioned above and also from the diagram, it's only when the
>>>>>> plan evaluated using either SQL patch/stored outline/SQL profile/hints, etc
>>>>>> matches one of the sql_handle with accepted=YES and enabled=YES, then that
>>>>>> plan will be in use. *
>>>>>>
>>>>>> Correct.
>>>>>>
>>>>>> *But in case none of the accepted=yes and enabled=yes SQL plan
>>>>>> baseline matches with the incoming parsed plan which is generated using SQL
>>>>>> patch/hints/SQL profile, then that new plan will be added to the SQL plan
>>>>>> baseline list with enabled=yes but accepted=NO and will be in use*
>>>>>>
>>>>>> It’s not exactly correct
>>>>>>
>>>>>> If the incoming parsed plan which is generated using SQL
>>>>>> patch/hints/SQL profile does not match one of the accepted=yes and
>>>>>> enabled=yes SQL plan baselines, then that new plan will be added to the SQL
>>>>>> plan baseline list with enabled=yes but accepted=NO and
>>>>>>
>>>>>> 1) All the accepted and enabled SQL plan baselines will be
>>>>>> reproduced and costed. The best costed and reproduced plan will be used
>>>>>>
>>>>>> 2) If none of the accepted and enabled SQL plan baselines is
>>>>>> *reproducible* then the CBO plan added to the SQL plan baseline list
>>>>>> with enabled=yes but accepted=NO will be used
>>>>>>
>>>>>> *My thought initially was, if the incoming parsed plan(which has came
>>>>>> through the profile/patch/hints etc..) doesn't match with any of the
>>>>>> baselines( accepted=yes and enabled=yes) baselines then, it should go back
>>>>>> and use one of the baselines( accepted=yes and enabled=yes) having lower
>>>>>> cost only. *
>>>>>>
>>>>>> Correct provided the SPM baseline plans are reproducible
>>>>>>
>>>>>> Best regards
>>>>>>
>>>>>> Mohamed
>>>>>>
>>>>>> Le ven. 17 déc. 2021 à 15:57, Lok P <loknath.73_at_gmail.com> a écrit :
>>>>>>
>>>>>>> Trying to interpret the diagram in Mohamed's blog it looks like a
>>>>>>> two step process 1) It will try to use your hints/sql profile/patches etc
>>>>>>> to come up with a plan and then the plan must match with one of the plans
>>>>>>> in the sql plan baseline having accepted=yes and enabled=yes. but in case
>>>>>>> it doesn't match then it will use its own mind(i.e. optimizer default
>>>>>>> setups without considering those hints/profile/patches) and come up with a
>>>>>>> different plan altogether which will be in use even A*ccepted='NO'*
>>>>>>> for this new plan in the sql plan baseline list. And this is where things
>>>>>>> can get screwed up , if someone creates a sql profile/sql
>>>>>>> patch thinking that their plan is going to be used :). .In short sql
>>>>>>> profile/patches/hints can totally be ignored and give you surprises if you
>>>>>>> create baselines for that sql. So, does that mean there is no guarantee
>>>>>>> that the plan which we have set through the sql plan baseline will be used
>>>>>>> 100% of the time.?
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Dec 17, 2021 at 8:06 PM Pap <oracle.developer35_at_gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Thank you so much Mohamed.
>>>>>>>>
>>>>>>>> *"When generating this execution plan, all things being equal,
>>>>>>>> Oracle will use the set of hints contained in the SQL Profile or the SQL
>>>>>>>> patch. It is only when the execution plan has been generated that the SPM
>>>>>>>> finally comes into play."*
>>>>>>>>
>>>>>>>> As you mentioned above and aso from the diagram , it's only when
>>>>>>>> the plan evaluated using either sql patch/stored outline/sqlprofile/hints
>>>>>>>> etc matches one of the sql_handle with accepted=YES and enabled=YES, then
>>>>>>>> that plan will be in use. But in case none of the accepted=yes and
>>>>>>>> enabled=yes sql plan baseline matches with the incoming parsed plan which
>>>>>>>> is generated using sql patch/hints/sql profile, then that new plan will be
>>>>>>>> added to the sql plan baseline list with enabled=yes but accepted=NO, and
>>>>>>>> will be in use. And in this case isn't it incorrect for Oracle to use a
>>>>>>>> plan which has not been accepted by us in the sql plan baseline? Am I
>>>>>>>> missing anything here?And if this is true , then there is no guarantee that
>>>>>>>> the enabled and accepted baselines are always going to be the only plans to
>>>>>>>> be in use by the sql optimizer.
>>>>>>>>
>>>>>>>> My thought initially was , if the incoming parsed plan(which has
>>>>>>>> came through the profile/patch/hints etc..) doesn't match with any of the
>>>>>>>> baselines( accepted=yes and enabled=yes) baselines then , it should go back
>>>>>>>> and use one of the baselines( accepted=yes and enabled=yes) having lower
>>>>>>>> cost only.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Fri, Dec 17, 2021 at 3:17 PM Mohamed Houri <
>>>>>>>> mohamed.houri_at_gmail.com> wrote:
>>>>>>>>
>>>>>>>>> Hello
>>>>>>>>>
>>>>>>>>> *In other words the sql profile or sql patch helps to create new
>>>>>>>>> plans based on the embedded hints however presence of baseline with
>>>>>>>>> accepted +enabled flag as 'YES' is always going to rule at the end. Correct
>>>>>>>>> if my understanding is wrong here?*
>>>>>>>>>
>>>>>>>>> The underlined part is incorrect
>>>>>>>>>
>>>>>>>>> If you look at the picture I designed in this article
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-interaction/
>>>>>>>>>
>>>>>>>>> You will realize that the presence of an SPM has *no influence on
>>>>>>>>> the ordinary work of the CBO*. Initially, Oracle will produce its
>>>>>>>>> execution plan ignoring the presence or absence of an SPM.
>>>>>>>>>
>>>>>>>>> When generating this execution plan, all things being equal,
>>>>>>>>> Oracle will use the set of hints contained in the SQL Profile or the SQL
>>>>>>>>> patch. *It is only when the execution plan has been generated
>>>>>>>>> that the SPM finally comes into play.*
>>>>>>>>>
>>>>>>>>> And what exactly will this SPM do?
>>>>>>>>>
>>>>>>>>> It will simply ensure that only the execution plan contained in
>>>>>>>>> the SPM baseline will be used regardless of how this plan has been
>>>>>>>>> generated, via SQL Profile, via SQL patch, or without any of those two
>>>>>>>>> features.
>>>>>>>>>
>>>>>>>>> Of course, this insurance against a bad surprise(a non-desired
>>>>>>>>> execution plan) is only possible if the plan in the SPM is reproducible at
>>>>>>>>> the time of the execution of the request.
>>>>>>>>>
>>>>>>>>> Hope this is clear
>>>>>>>>>
>>>>>>>>> Best regards
>>>>>>>>>
>>>>>>>>> Mohamed Houri
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Le ven. 17 déc. 2021 à 05:18, Pap <oracle.developer35_at_gmail.com>
>>>>>>>>> a écrit :
>>>>>>>>>
>>>>>>>>>> Thank you so much Andy.
>>>>>>>>>>
>>>>>>>>>> If I get it correct, it's the sql profile(which is generally a
>>>>>>>>>> combination of opt_estimate hints) which oracle will use to evaluate the
>>>>>>>>>> plans but it's ultimately the sqlplan baselines (which must be in
>>>>>>>>>> accepted+enabled) state which is going to be used finally.
>>>>>>>>>>
>>>>>>>>>> And in scenarios where we use technique to manually create sql
>>>>>>>>>> profile by passing exact outline hints (but not with opt_etsimate hints)
>>>>>>>>>> i.e. using procedure dbms_sqltune.import_sql_profile where we
>>>>>>>>>> have ability to pass the exact full query outline hints which restricts the
>>>>>>>>>> execution path to one. In those cases it will mostly fix the exact one
>>>>>>>>>> execution path for the query no matter how many times it parses. And in the
>>>>>>>>>> presence of such a sql profile , if the sql plan baseline has exactly the
>>>>>>>>>> same path enabled+accepted that is going to be used or else it's going to
>>>>>>>>>> be completely discarded and the priority will be given to the baseline
>>>>>>>>>> which is in accepted+enabled state. (And the discarded plan i.e the parsed
>>>>>>>>>> plan using sql profile may be added a new baseline to the list with
>>>>>>>>>> accepted flag as NO). Which means sql plan baseline having
>>>>>>>>>> enabled+accepted flag as "YES" will take the precedence always irrespective
>>>>>>>>>> of sql profile/sq patch etc. Is this understanding correct?
>>>>>>>>>> Or
>>>>>>>>>> In other words the sql profile or sql patch helps to create new
>>>>>>>>>> plans based on the embedded hints however presence of baseline with
>>>>>>>>>> accepted +enabled flag as 'YES' is always going to rule at the end. Correct
>>>>>>>>>> if my understanding is wrong here?
>>>>>>>>>>
>>>>>>>>>> On Fri, Dec 17, 2021 at 1:53 AM Andy Klock <andy_at_klockmail.com>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi Laurentiu,
>>>>>>>>>>>
>>>>>>>>>>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>>>>>>>>>>> On Thursday, December 16th, 2021 at 3:08 PM, Pap <
>>>>>>>>>>> oracle.developer35_at_gmail.com> wrote:
>>>>>>>>>>>
>>>>>>>>>>> So in cases where the SQL profile or SQL patch has some hints
>>>>>>>>>>> (say FTS on tab1) which is exactly opposite than that of the one in SQL
>>>>>>>>>>> plan baseline(say index access for tab1). In that scenario will oracle
>>>>>>>>>>> follow profile/patch or the baseline path?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> If a cursor has "accepted" baselines, then that is typically
>>>>>>>>>>> what the CBO is going to prefer. When there are multiple accepted plans,
>>>>>>>>>>> then the CBO will pick the plan with the lowest cost. For cursors that also
>>>>>>>>>>> have a SQL Profile, then those hints and opt_estimates are applied which
>>>>>>>>>>> may affect the cost of the baseline plans that the CBO will pick.
>>>>>>>>>>>
>>>>>>>>>>> Maria wrote a nice article about it [1] in 2012 which I think is
>>>>>>>>>>> still relevant, but often when you have cursors with lots of baselines, SQL
>>>>>>>>>>> Profiles, and patches it's time to fix the stats or change the code :)
>>>>>>>>>>>
>>>>>>>>>>> Andy K
>>>>>>>>>>>
>>>>>>>>>>> [1]
>>>>>>>>>>> https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>>
>>>>>>>>> Houri Mohamed
>>>>>>>>>
>>>>>>>>> Oracle DBA-Developer-Performance & Tuning
>>>>>>>>>
>>>>>>>>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>>>>>>>>
>>>>>>>>> Let's Connect -
>>>>>>>>> <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>>>>>>>>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>>>>>>>>
>>>>>>>>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>>>>>>>>> <https://twitter.com/MohamedHouri>
>>>>>>>>>
>>>>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Houri Mohamed
>>>>>>
>>>>>> Oracle DBA-Developer-Performance & Tuning
>>>>>>
>>>>>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>>>>>
>>>>>> Let's Connect -
>>>>>> <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>>>>>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>>>>>
>>>>>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>>>>>> <https://twitter.com/MohamedHouri>
>>>>>>
>>>>>>
>>>>
>>>> --
>>>>
>>>> Houri Mohamed
>>>>
>>>> Oracle DBA-Developer-Performance & Tuning
>>>>
>>>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>>>
>>>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>>>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>>>
>>>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>>>> <https://twitter.com/MohamedHouri>
>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l