*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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxxxxxx>
wrote:
Hi Laurentiu,
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, December 16th, 2021 at 3:08 PM, Pap <
oracle.developer35@xxxxxxxxx> 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>
