Re: Plan changed After creating profile
Date: Fri, 1 Oct 2021 23:17:41 +0530
Message-ID: <CAO8FHeXb+EnZz4--RqwKySoELqh7uC0VEPrS=2s-CYhpfcFw9g_at_mail.gmail.com>
Hi All,
Below is extract from Hint :
PLAN_ID HINT
PLAN_NAME SIGNATURE OPT_TYPE CATEGORY
---------- ---------- -------------------------------- --------------------
---------- --------------------------------------------------
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 OPT_ESTIMATE(_at_"SEL$2", INDEX_SCAN, "AUDITTRA
F_014d7f18
IL"_at_"SEL$2", "IX_AUDITNAME", SCALE_ROWS=127210
a95d0000
.5501)
SYS_SQLPRO 1.3066E+19 Profile DEFAULT
0 OPT_ESTIMATE(_at_"SEL$2", INDEX_SKIP_SCAN, "AUD
F_014d7f18
ITTRAIL"_at_"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_at_gmail.com> 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_at_gmail.com>
> 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_at_gmail.com>
>> 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_at_gmail.com>
>>> 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_at_gmail.com>
>>>> 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
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 01 2021 - 19:47:41 CEST