Re: Priority of profile baseline patch

*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