Re: SQL Profile and baseline

Amir

I am totally in phase with Dom. And that's why* I advise you to stay with this SQL Profile if you have found that it does its job*.

That’s said, there are situations where a SPM is self-evident. I want to talk about the case where you have a parsing problem (yes, I'm talking about parsing 😀 ) but a parsing only due to ACS. In this case and only in this particular case of ACS, and from 12.2 onwards, if you set an SPM your query becomes not subject to ACS and stops causing parsing issues.

And even in this particular situation, if you are in 19c and above you should also make sure to prevent Oracle from *evolving, automatically, new plans in the SPM*. Indeed, if a new plan is added by Oracle to your SPM baseline, Oracle will consider again that your query is likely to benefit from ACS and, the parsing problem will reappear with greater severity than before.

To prevent Oracle from evolving your SPM plans you should make sure that the value of the following parameter is set to FALSE

SELECT     parameter_name,
    parameter_value
FROM
    dba_advisor_parameters
WHERE
        task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'     AND parameter_name = 'ACCEPT_PLANS'
;

PARAMETER_NAME PARAMETER_VALUE

------------------ ----------------
ACCEPT_PLANS       TRUE

SELECT
    parameter_name,
    parameter_value
FROM
    dba_advisor_parameters
WHERE
        task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'     AND parameter_name = 'ACCEPT_PLANS'
;

BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ACCEPT_PLANS',
      value => 'FALSE');

PARAMETER_NAME PARAMETER_VALUE

---------------- ----------------
ACCEPT_PLANS     FALSE

Best regards

Mohamed

Le mer. 2 févr. 2022 à 01:20, Mladen Gogala <gogala.mladen_at_gmail.com> a écrit :

> On 2/1/22 13:06, Hameed, Amir wrote:
>
> Hi,
>
> As a general guideline, once a SQL profile provides an optimal execution
> plan for a SQL, is it okay to create a baseline for that SQL and then drop
> the SQL Profile? The DB version is 11.2.0.4. Any feedback will be
> appreciated.
>
>
>
> Thanks,
>
> Amir
>
> Hi Amir,
>
> Both baselines and profiles are essentially the same thing: a bunch of
> hints. In my experience, profiles provide more stable and overall better
> mechanism for achieving plan stability. Baselines are poor man's profiles.
> However, if you want to nail the plan to the wall, you can also create an
> outline, which is an actual execution plan which doesn't change or evolve
> but stays the same forever and ever, until "drop database" takes it apart.
> Outlines are deprecated but still supported. However, I wouldn't advise
> using outlines precisely because their inability to evolve.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l

-- 

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