Re: Priority of profile baseline patch

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