SQL Baselines
Baseline Auto Capture
Notes
Oracle recommends that you don’t switch on auto-capture indefinitely. Instead, capture new queries when changes have been made to applications and workloads. Bulk capture (from the cursor cache or from SQL tuning sets) is a good on-going approach too.
If your application uses literal variables instead of sharable SQL, then consider setting the CURSOR_SHARING parameter to SIMILAR in an attempt to avoid an unmanageable number of baselines being created (each the same except for the literal values).
Check
SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
if TRUE then Auto Capture is EnabledSHOW PARAMETER OPTIMIZER_USE_SQL_PLAN_BASELINES
should be TRUE (if FALSE, then all Baselines will be ignored)Enable
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE SCOPE=BOTH;
Disable
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE SCOPE=BOTH;
NOTE: Even if OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to FALSE the optimizer will still auto capture new baselines for SQL where Enabled baselines already exist... unless one of the baselines is marked as Fixed.Baseline Retention
You should always adjust the retention when starting to play with SQL Plan Management as the default retention of 53 weeks may lead to a too large LOB segment in SYSAUX tablespace (and LOB segments never shrink). Mike Dietrich - Oracle Master Product Manager
Check
COLUMN parameter_name FORMAT a40
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
Recommended Configuration
exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5);
exec DBMS_SPM.CONFIGURE('space_budget_percent',10);
Baseline Auto Evolve
12c introduces an overnight job (triggered by the existing "sql tuning advisor" client under the automated database maintenance tasks) that will automatically Evolve baselines unless you explicitly disable this functionality.
Check
COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a10
SELECT PARAMETER_NAME,
PARAMETER_VALUE AS "VALUE"
FROM DBA_ADVISOR_PARAMETERS
WHERE (
( TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK')
AND
(
( PARAMETER_NAME = 'ACCEPT_PLANS')
OR
( PARAMETER_NAME = 'LOCAL_TIME_LIMIT')
OR
( PARAMETER_NAME = 'TIME_LIMIT')
)
);
Disable
Automatic Evolve may be appropriate for your database. Only Disable this functionality if you have justification.
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
parameter => 'ACCEPT_PLANS' ,
value => 'false' );
END;
/
Enable
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
parameter => 'ACCEPT_PLANS' ,
value => 'true' );
END;
/
VIEW Baselines
Baseline Summary
SET PAGESIZE 60
SET LINESIZE 250
COLUMN SQL_HANDLE FORMAT a20
COLUMN PLAN_NAME FORMAT a30
COLUMN CREATOR FORMAT a8
COLUMN CREATED FORMAT a29
COLUMN LAST_MODIFIED FORMAT a29
COLUMN LAST_EXECUTED FORMAT a29
SELECT SQL_HANDLE,
PLAN_NAME,
EXECUTIONS,
CREATOR,
ORIGIN,
CREATED,
LAST_MODIFIED,
LAST_EXECUTED,
ENABLED,
ACCEPTED,
FIXED,
REPRODUCED,
AUTOPURGE
FROM dba_sql_plan_baselines
ORDER BY CREATED
/
SQL_TEXT for a Baseline
SELECT plan_name, sql_text
FROM dba_sql_plan_baselines
WHERE plan_name = '&PLAN_NAME'
/
OUTLINE Data for a Baseline
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => '&PLAN_NAME', FORMAT => 'OUTLINE'));
Or for slightly more information...
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => '&PLAN_NAME', FORMAT => 'ADVANCED'));
Or to display OUTLINEs for all captured Baselines...
SELECT t.*
FROM ( SELECT DISTINCT plan_name
FROM dba_sql_plan_baselines ) pb,
TABLE(dbms_xplan.display_sql_plan_baseline(PLAN_NAME => pb.plan_name, FORMAT => 'OUTLINE')) t;
DROP Baseline
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => '&SQL_HANDLE',
plan_name => '&PLAN_NAME');
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
Bibliography
https://blogs.oracle.com/optimizer/are-my-sql-plan-baselines-being-re-enabled https://blogs.oracle.com/optimizer/oracle-database-19c-and-sql-plan-management-diagnostics
http://psoug.org/reference/dbms_xplan.html http://kerryosborne.oracle-guy.com/2009/04/29/oracle-11g-sql-plan-management-sql-plan-baselines/
https://dohdatabase.com/2023/09/18/is-it-possible-to-migrate-sql-plan-baselines-before-you-migrate-data/
How to Display SPM/SQL Plan Baseline Plans Outlines Data(Hints) (Doc ID 2220257.1)