Re: Priority of profile baseline patch

Hi Mohamed, Laurentiu,

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, December 17th, 2021 at 4:47 AM, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> 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.

It's a nice picture and does show some of the CBO's "ordinary work", but doesn't it also show that in the end the CBO is being influenced (aka "ruled" :) ) by the accepted plans that the CBO was able to reproduce?

Generally (and in my experience, pretty much always), if there are SQL Plan Baselines in place then SQL Profiles are not needed at all. I can think of some theoretical scenarios when there are multiple accepted baselines and if you wanted to tip the scale for a particular plan then a profile would be a nice tool for that. However, Laurentiu mentioned that the opt_estimates are being left out, so I would need some more details as to why the SQL Profiles are thought to be needed.

Good stuff!

Andy K

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