Re: Plan changed After creating profile

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