Re: Plan changed After creating profile

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>

--
http://www.freelists.org/webpage/oracle-l