Re: Priority of profile baseline patch
- From: Andy Klock <andy@xxxxxxxxxxxxx>
- To: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- Date: Fri, 17 Dec 2021 13:48:56 +0000
Hi Mohamed, Laurentiu,
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, December 17th, 2021 at 4:47 AM, Mohamed Houri
<mohamed.houri@xxxxxxxxx> 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
Other related posts:
