Re: Priority of profile baseline patch

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>

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