Adric,
Your explanation doesn't sound quite the way I had imagined it would.
In the absence of a profile the optimizer might take a long time to come up
with a bad plan before it looks at the SMB and uses a good plan. (This is
Mohamed's point). The way I read your warning was that with a profile in
place even though the optimizer might STILL come up with a bad plan, it
might do it very quickly and therefore be able to pick up a good plan from
the SMB very quickly.
I've said that in this type of case I'd get the baseline and push it into a
patch - I think that most people tend to use the content of the baseline
and push it into a profile. (A practice I don't like because profiles and
baselines do look and behave differently.)
There's the extra complication, of course, that there are still cases where
a baseline will not reproduce the plan that created the baseline because of
defects in the way the baseline was generated, or limitations in the way
that the hints can be expressed. That's (partly) why several hints have had
their syntax extended over the last few years.
Regards
Jonathan Lewis
On Thu, 30 Sept 2021 at 17:53, Adric Norris <landstander668@xxxxxxxxx>
wrote:
The reason is fairly simple. The optimizer is time-constrained when
generating potential execution plans, and if the plan fixed by a SQL
baseline isn't a one which the optimizer evaluates at parse time it simply
gets ignored. Occasionally the desired plan isn't something the optimizer
will reproduce without taking the cost/cardinaluty adjustments introduced
by the profile into account.
While it's not common (in my experience), this absolutely happens.
On Thu, Sep 30, 2021 at 10:52 AM Mohamed Houri <mohamed.houri@xxxxxxxxx>
wrote:
Adric,
I do not see why the presence of a SQL profile becomes essential for the
reproduction of an SPM. The two concepts are totally different. Where a SQL
Profile tells the CBO to use a set of hints, SPM does not interfere with
the CBO's work at all. That's why Jonathan said *“**if you've got a
query that takes a long time to optimize a Baseline won't address that part
of the problem”*
If the CBO, with the help of the SQL profile (or not), produces an
execution plan that is contained in the SPM baseline it will be used. If
the CBO plan is not in the SPM baseline (phv2 != plan_id), then the plan
(or plans) in the SPM will be reproduced; and if so, the SPM will be used.
So even in the presence of a SQL profile no other plan than the one imposed
by the SPM and *reproducible* (of course) will be accepted and used.
Best regards
Mohamed Houri
Le jeu. 30 sept. 2021 à 16:26, Jonathan Lewis <jlewisoracle@xxxxxxxxx> a
écrit :
Adric,
Useful warning.
Somewhere I have a note that points out that when you have an SQL P,lan
Baseline in place the first thing the optimizer does is optimize the query
without looking at the Baseline; so if you've got a query that takes a long
time to optimize a Basline won't address that part of the problem and a
"cunning plan" is to create the Baseline that works well, then convert it
to an SQL Patch (with a couple of calls to the relevant package).
Regards
Jonathan Lewis
On Thu, 30 Sept 2021 at 14:52, Adric Norris <landstander668@xxxxxxxxx>
wrote:
Generally people seem to find SQL Plan Baslines more stable than SQL
Profiles (but I can't quantify that, I only have anecdotal evidence) - so
if you've generated a SQL Profile it might be a good idea to generate an
SQL Plan Basline after running the query, accept it and fix it, then
delete
the profile.
I've seen multiple situations where the plan fixed by the baseline
can't be reproduced (within the time constraints the optimizer is faced
with) unless the profile is present and enabled. So there's a chance that
the profile will have to be left in place.
--
"In the beginning the Universe was created. This has made a lot of
people very angry and been widely regarded as a bad move." -Douglas Adams
--
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>
--
"In the beginning the Universe was created. This has made a lot of people
very angry and been widely regarded as a bad move." -Douglas Adams
