SQL Baselines

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 Enabled

SHOW 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

COLUMN PARAMETER_NAME FORMAT a25

COLUMN 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

/

SELECT sql_handle, plan_name, enabled, accepted

FROM dba_sql_plan_baselines

WHERE sql_text LIKE '&StartOfSQL%';

SQL_HANDLE PLAN_NAME ENA ACC------------------------------ ------------------------------ --- ---SQL_d799385360b4794a SQL_PLAN_dg69sadhb8yaa4fce12c4 YES YESSQL_d799385360b4794a SQL_PLAN_dg69sadhb8yaab56e00c4 NO YES

SQL_TEXT for a Baseline

SELECT plan_name, sql_text

FROM dba_sql_plan_baselines

WHERE plan_name = '&PLAN_NAME'

/

EXPLAIN PLAN for a Baseline

SET LONG 10000

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'&PLAN_NAME'));

PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------SQL handle: SQL_d799385360b4794aSQL text: UPDATE DETAILS D SET D.FILENAME = (SELECT F.FILENAME FROM FILES F WHERE F.TYPE = D.TYPE AND F.LOAD_ID = D.LOAD_ID AND UPPER(REPLACE(DECODE(D.BOOM,'TNT','Dynamite',D.BOOM),' ')) = UPPER(F.BOOM)) WHERE LOAD_ID = :B1--------------------------------------------------------------------------------
--------------------------------------------------------------------------------Plan name: SQL_PLAN_dg69sadhb8yaa4fce12c4 Plan id: 1338905284Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE--------------------------------------------------------------------------------
Plan hash value: 2899885385
-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 7 | 210 | 29957 (1)| 00:06:00 || 1 | UPDATE | DETAILS | | | | || 2 | TABLE ACCESS BY INDEX ROWID| DETAILS | 7 | 210 | 4 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | DETAILS_IDX4 | 7 | | 3 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| FILES | 1 | 76 | 4279 (1)| 00:00:52 ||* 5 | INDEX RANGE SCAN | FILES_IDX | 18421 | | 162 (1)| 00:00:02 |-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("LOAD_ID"=TO_NUMBER(:B1)) 4 - filter("F"."LOAD_ID"=:B1 AND UPPER("F"."BOOM")=UPPER(REPLACE(DECODE(:B2,'TNT' ,'Dynamite',:B3),' '))) 5 - access("F"."TYPE"=:B1)

PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------SQL handle: SQL_d799385360b4794aSQL text: UPDATE DETAILS D SET D.FILENAME = (SELECT L.FILENAME FROM FILES F WHERE F.TYPE = D.TYPE AND F.LOAD_ID = D.LOAD_ID AND UPPER(REPLACE(DECODE(D.BOOM,'TNT','Dynamite',D.BOOM),' ')) = UPPER(F.BOOM)) WHERE LOAD_ID = :B1--------------------------------------------------------------------------------
--------------------------------------------------------------------------------Plan name: SQL_PLAN_dg69sadhb8yaab56e00c4 Plan id: 3043885252Enabled: NO Fixed: NO Accepted: YES Origin: AUTO-CAPTURE--------------------------------------------------------------------------------
Plan hash value: 2909200407
--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 7 | 210 | 97503 (3)| 00:19:31 || 1 | UPDATE | DETAILS | | | | ||* 2 | TABLE ACCESS FULL | DETAILS | 7 | 210 | 67550 (4)| 00:13:31 ||* 3 | TABLE ACCESS BY INDEX ROWID| FILES | 1 | 76 | 4279 (1)| 00:00:52 ||* 4 | INDEX RANGE SCAN | FILES_IDX | 18421 | | 162 (1)| 00:00:02 |--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - filter("LOAD_ID"=TO_NUMBER(:B1)) 3 - filter("F"."LOAD_ID"=:B1 AND UPPER("F"."BOOM")=UPPER(REPLACE(DECODE(:B2, 'TNT','Dynamite',:B3),' '))) 4 - access("F"."TYPE"=:B1)
34 rows selected.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => '&PLAN_NAME', FORMAT => 'OUTLINE'));

PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------SQL handle: SQL_d799385360b4794aSQL text: UPDATE DETAILS D SET D.FILENAME = (SELECT F.FILENAME          FROM FILES F WHERE F.TYPE = D.TYPE AND          F.LOAD_ID = D.LOAD_ID AND          UPPER(REPLACE(DECODE(D.BOOM,'TNT','Dynamite',D.BOOM),' ')) =          UPPER(F.BOOM)) WHERE LOAD_ID = :B1--------------------------------------------------------------------------------
--------------------------------------------------------------------------------Plan name: SQL_PLAN_dg69sadhb8yaab56e00c4 Plan id: 3043885252Enabled: NO Fixed: NO Accepted: YES Origin: AUTO-CAPTURE--------------------------------------------------------------------------------
--------------------------------------------------------------------------------Outline Data from SMB:
  /*+      BEGIN_OUTLINE_DATA      INDEX_RS_ASC(@"SEL$1" "F"@"SEL$1" ("FILES"."TYPE"))      FULL(@"UPD$1" "D"@"UPD$1")      OUTLINE_LEAF(@"UPD$1")      OUTLINE_LEAF(@"SEL$1")      ALL_ROWS      DB_VERSION('11.2.0.3')      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */--------------------------------------------------------------------------------
Plan hash value: 2909200407
--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 7 | 210 | 97503 (3)| 00:19:31 || 1 | UPDATE | DETAILS | | | | ||* 2 | TABLE ACCESS FULL | DETAILS | 7 | 210 | 67550 (4)| 00:13:31 ||* 3 | TABLE ACCESS BY INDEX ROWID| FILES | 1 | 76 | 4279 (1)| 00:00:52 ||* 4 | INDEX RANGE SCAN | FILES_IDX | 18421 | | 162 (1)| 00:00:02 |--------------------------------------------------------------------------------------------------
Outline Data-------------
  /*+      BEGIN_OUTLINE_DATA      INDEX_RS_ASC(@"SEL$1" "F"@"SEL$1" ("FILES"."TYPE"))      FULL(@"UPD$1" "D"@"UPD$1")      OUTLINE_LEAF(@"UPD$1")      OUTLINE_LEAF(@"SEL$1")      ALL_ROWS      DB_VERSION('11.2.0.3')      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */
Predicate Information (identified by operation id):---------------------------------------------------
   2 - filter("LOAD_ID"=TO_NUMBER(:B1))   3 - filter("F"."LOAD_ID"=:B1 AND UPPER("F"."BOOM")=UPPER(REPLACE(DECODE(:B2,              'TNT','Dynamite',:B3),' ')))   4 - access("F"."TYPE"=:B1)

Or for slightly more information...

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => '&PLAN_NAME', FORMAT => 'ADVANCED'));

PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------SQL handle: SQL_d799385360b4794aSQL text: UPDATE DETAILS D SET D.FILENAME = (SELECT F.FILENAME          FROM FILES F WHERE F.TYPE = D.TYPE AND          F.LOAD_ID = D.LOAD_ID AND          UPPER(REPLACE(DECODE(D.BOOM,'TNT','Dynamite',D.BOOM),' ')) =          UPPER(F.CARRIER)) WHERE LOAD_ID = :B1--------------------------------------------------------------------------------
--------------------------------------------------------------------------------Plan name: SQL_PLAN_dg69sadhb8yaab56e00c4 Plan id: 3043885252Enabled: NO Fixed: NO Accepted: YES Origin: AUTO-CAPTURE--------------------------------------------------------------------------------
--------------------------------------------------------------------------------Outline Data from SMB:
  /*+      BEGIN_OUTLINE_DATA      INDEX_RS_ASC(@"SEL$1" "L"@"SEL$1" ("FILES"."TYPE"))      FULL(@"UPD$1" "D"@"UPD$1")      OUTLINE_LEAF(@"UPD$1")      OUTLINE_LEAF(@"SEL$1")      ALL_ROWS      DB_VERSION('11.2.0.3')      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */--------------------------------------------------------------------------------
Plan hash value: 2909200407
--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 7 | 210 | 97503 (3)| 00:19:31 || 1 | UPDATE | DETAILS | | | | ||* 2 | TABLE ACCESS FULL | DETAILS | 7 | 210 | 67550 (4)| 00:13:31 ||* 3 | TABLE ACCESS BY INDEX ROWID| FILES | 1 | 76 | 4279 (1)| 00:00:52 ||* 4 | INDEX RANGE SCAN | FILES_IDX | 18421 | | 162 (1)| 00:00:02 |--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------
   1 - UPD$1   2 - UPD$1 / CD@UPD$1   3 - SEL$1 / L@SEL$1   4 - SEL$1 / L@SEL$1
Outline Data-------------
  /*+      BEGIN_OUTLINE_DATA      INDEX_RS_ASC(@"SEL$1" "L"@"SEL$1" ("FILES"."TYPE"))      FULL(@"UPD$1" "D"@"UPD$1")      OUTLINE_LEAF(@"UPD$1")      OUTLINE_LEAF(@"SEL$1")      ALL_ROWS      DB_VERSION('11.2.0.3')      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */
Predicate Information (identified by operation id):---------------------------------------------------
   2 - filter("LOAD_ID"=TO_NUMBER(:B1))   3 - filter("F"."LOAD_ID"=:B1 AND UPPER("F"."BOOM")=UPPER(REPLACE(DECODE(:B2,              'TNT','Dynamite',:B3),' ')))   4 - access("F"."TYPE"=:B1)
Column Projection Information (identified by operation id):-----------------------------------------------------------
   2 - (upd=2,6; cmp=2,3,4,5) "D".ROWID[ROWID,10], "D"."SSC"[VARCHAR2,5],       "D"."BOOM"[VARCHAR2,20], "LOAD_ID"[NUMBER,22],       "D"."TYPE"[VARCHAR2,1], "D"."FILENAME"[VARCHAR2,200]   3 - "F".ROWID[ROWID,10], "F"."FILENAME"[VARCHAR2,200], "F"."BOOM"[VARCHAR2,50],       "F"."TYPE"[VARCHAR2,20], "F"."LOAD_ID"[NUMBER,22]   4 - "F".ROWID[ROWID,10], "F"."TYPE"[VARCHAR2,20]

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;

Evolve Baseline

SET LONG 10000

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&SQLHANDLE')

FROM dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_608BB3A76D89E309')--------------------------------------------------------------------------------
-------------------------------------------------------------------------------                        Evolve SQL Plan Baseline Report-------------------------------------------------------------------------------
Inputs:-------  SQL_HANDLE = SQL_608bb3a76d89e309  PLAN_NAME =  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT  VERIFY = YES  COMMIT = YES
Plan: SQL_PLAN_612xmnxqsmss965336f21------------------------------------  Plan was verified: Time used 28.718 seconds.  Plan passed performance criterion: 70947.88 times better than baseline plan.  Plan was changed to an accepted plan.
                            Baseline Plan Test Plan Stats Ratio                            ------------- --------- -----------  Execution Status: COMPLETE COMPLETE  Rows Processed: 1 1  Elapsed Time(ms): 8471.749 .03 282391.63  CPU Time(ms): 1190.499 .015 79366.6  Buffer Gets: 212831 3 70943.67  Physical Read Requests: 1552 0  Physical Write Requests: 0 0  Physical Read Bytes: 193863680 0  Physical Write Bytes: 0 0  Executions: 1 1
-------------------------------------------------------------------------------                                 Report Summary-------------------------------------------------------------------------------Number of plans verified: 1Number of plans accepted: 1

DISABLE Baseline

SET SERVEROUTPUT ON

DECLARE

l_plans_altered PLS_INTEGER;

BEGIN

l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

sql_handle => '&SQL_HANDLE',

plan_name => '&PLAN_NAME',

attribute_name => 'enabled',

attribute_value => 'NO');

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

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;

/

SQLT

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://oracle-base.com/articles/11g/sql-plan-management-11gr1
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)