Forum Stats

  • 3,758,202 Users
  • 2,251,352 Discussions
  • 7,870,107 Comments

Discussions

Question on SQL Plan Baselines

BlueSkies
BlueSkies Member Posts: 73 Blue Ribbon
edited Sep 21, 2021 8:32AM in General Database Discussions

DB version : 12.1.0.2.0


I am kind of new to SQL Plan Baselines 



Question 1. Is it wise to disable auto capture of SQL Plan baselines ?

I have noticed in my 12c (12.1.0.2.0) databases, all SQLs seem to be using auto captured baselines and I have the following setting 


SQL> show parameter baselines

PARAMETER_NAME                        TYPE    VALUE
------------------------------------------------------------ ----------- --------------------------
optimizer_capture_sql_plan_baselines             boolean   TRUE
optimizer_use_sql_plan_baselines               boolean   TRUE


Why does Oracle do it ? 

Is it because Oracle does not want a plan to be flushed off from the shared pool in scenarios like gathering stats with "no_invalidate => false" ?


So, Is it wise to disable auto capture of SQL Plan baselines by setting optimizer_capture_sql_plan_baselines parameter to FALSE ?


I want to disable it because I don't see anything wrong with the good old method of letting Oracle generate a plan and then store that plan (not baseline) in the shared pool and re-use it. I am not against baselines. But, I want to use only manually loaded baselines. Is that stupid decision ?


Question 2

If the following query returns no rows, is it safe to assume that no manually created baselines exist for queries that refer tables in SIEBEL schema ?


SELECT *
FROM  dba_sql_plan_baselines
WHERE parsing_schema_name = 'SIEBEL'
AND  origin != 'AUTO-CAPTURE' ;

Best Answer

  • JohnWatson2
    JohnWatson2 Member Posts: 4,315 Silver Crown
    Accepted Answer

    The default is optimizer_capture_sql_plan_baselines=false, so clearly your DBA has made a decision to capture and use baselines. If you think that they were wrong to do this, better discuss it with them.

    In general, enabling baselining should be a Good Thing. It will stabilize your system against any changes for the worse, and if you have the SYS_AUTO_SPM_EVOLVE_TASK (part of the Tuning Pack) running it will by default permit any changes for the better. Worst case is that improvements would be held back for 24 hours before being evolved. If you don't have the task running, you'll have to run your own job to detect and evolve any new exec plans. It's all pretty straightforward.

    BlueSkies

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,315 Silver Crown
    Accepted Answer

    The default is optimizer_capture_sql_plan_baselines=false, so clearly your DBA has made a decision to capture and use baselines. If you think that they were wrong to do this, better discuss it with them.

    In general, enabling baselining should be a Good Thing. It will stabilize your system against any changes for the worse, and if you have the SYS_AUTO_SPM_EVOLVE_TASK (part of the Tuning Pack) running it will by default permit any changes for the better. Worst case is that improvements would be held back for 24 hours before being evolved. If you don't have the task running, you'll have to run your own job to detect and evolve any new exec plans. It's all pretty straightforward.

    BlueSkies