Hi All,
Below is extract from Hint :
PLAN_NAME SIGNATURE OPT_TYPE CATEGORY
PLAN_ID HINT
---------- ---------- -------------------------------- --------------------
---------- --------------------------------------------------
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 OPT_ESTIMATE(@"SEL$2", INDEX_SCAN, "AUDITTRA
F_014d7f18
IL"@"SEL$2", "IX_AUDITNAME", SCALE_ROWS=127210
a95d0000
.5501)
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 OPT_ESTIMATE(@"SEL$2", INDEX_SKIP_SCAN, "AUD
F_014d7f18
ITTRAIL"@"SEL$2", "PK292", SCALE_ROWS=1623564.25)
a95d0000
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 COLUMN_STATS("TEst001"."AUDITTRAIL", "COL_ID", sc
F_014d7f18
ale, nulls=0 min=344257378839067955799897671980810
a95d0000
240 max=344257378839067955799897671980810240)
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 COLUMN_STATS("TEst001"."AUDITTRAIL", "AUDITEDID",
F_014d7f18
scale, nulls=0 min=0 max=58161137)
a95d0000
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 OPTIMIZER_FEATURES_ENABLE(default)
F_014d7f18
a95d0000
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 INDEX_STATS("TEst001"."AUDITTRAIL", "IX_AUDITED"
F_014d7f18
, scale, blocks=7300 index_rows=1541440.625)
a95d0000
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 INDEX_STATS("TEst001"."AUDITTRAIL", "IX_AUDITTRAIL
F_014d7f18
_AUDITEDID", scale, blocks=7804 index_rows=1639552
a95d0000
.115)
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 INDEX_STATS("TEst001"."AUDITTRAIL", "PK101", scale
F_014d7f18
, blocks=7174 index_rows=1653086.885)
a95d0000
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 TABLE_STATS("TEst001"."AUDITTRAIL", scale, blocks=
F_014d7f18
212300 rows=1623564.25)
a95d0000
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 COLUMN_STATS("TEst001"."AUDITTRAIL", "AUDITNAM
F_014d7f18
E", scale, nulls=0)
a95d0000
Regards,
Krishna
On Thu, 30 Sept 2021 at 22:57, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
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
