Question on SQL Plan Baselines

BlueSkies

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' ;

JohnWatson2

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.

JohnWatson2

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.