Re: Plan changed After creating profile

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-l