Embedded Files
Baseline Auto Capture
Baseline Auto Capture
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
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
Enable
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE SCOPE=BOTH;
Disable
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
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
Check
COLUMN parameter_name FORMAT a40
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
Recommended Configuration
Recommended Configuration
exec DBMS_SPM.CONFIGURE('plan_retention_weeks',5);
exec DBMS_SPM.CONFIGURE('space_budget_percent',10);
Baseline Auto Evolve
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
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
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
Enable
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
parameter => 'ACCEPT_PLANS' ,
value => 'true' );
END;
/
VIEW Baselines
VIEW Baselines
Baseline Summary
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
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
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)
--------------------------------------------------------------------------------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.
--------------------------------------------------------------------------------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)
--------------------------------------------------------------------------------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]
--------------------------------------------------------------------------------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
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
------------------------------------------------------------------------------- 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
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
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
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)
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)
Page updated
Google Sites
Report abuse