Oracle DBMS_SPM
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
 
Purpose SPM stands for SQL Plan Management and this package is part of the feature set introduced to support tuning advisor functionality.
AUTHID CURRENT_USER
Constants
Name Data Type Value
AUTO_LIMIT INTEGER 2147483647-1
NO_LIMIT INTEGER 2147483647
Data Types TYPE name_list IS TABLE OF VARCHAR2(30);
Dependencies
DBA_SQL_PLAN_BASELINES DBMS_SPM_LIB DUAL
DBMS_ADVISOR DBMS_SQLTUNE PLITBLM
DBMS_ASSERT DBMS_SQLTUNE_INTERNAL PRVT_ADVISOR
DBMS_LOB DBMS_SQLTUNE_UTIL1 SQLSET_ROW
DBMS_SMB DBMS_SYS_ERROR SQL_PLAN_TABLE_TYPE
DBMS_SMB_INTERNAL DBMS_XPLAN_LIB XMLTYPE
DBMS_SPM_INTERNAL    
Documented Yes
Exceptions
Error Code Reason
ORA-13602 The specified parameter <parameter_name> is not valid for task or object <task_name>.
ORA-13605 The specified task or object <string> does not exist for the current user.
ORA-13609 The specified task <string> must be executing to be cancelled or interrupted.
ORA-13631 The most recent execution of task <task_name> contains no results.
First Available 11.1.0.6
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsspm.sql
Subprograms
 
ACCEPT_SQL_PLAN_BASELINE (new 12.1)
Accept a plan based on the recommendation of an evolve task dbms_spm.accept_sql_plan_baseline(
task_name IN VARCHAR2,
object_id IN NUMBER,
task_owner IN VARCHAR2 := NULL,
force IN BOOLEAN := FALSE);
SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';

exec dbms_spm.accept_sql_plan_baseline('UWEVOLVE', 92053, 'UWCLASS');
 
ALTER_SQL_PLAN_BASELINE
Changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format dbms_spm.alter_sql_plan_baseline(
sql_handle      IN VARCHAR2 := NULL,
plan_name       IN VARCHAR2 := NULL,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2)
RETURN PLS_INTEGER;

Name Description Value
autopurge 'YES' means the plan is purged if it is not used for a time period. 'NO' means never purge 'YES' or 'NO'
description SQL Plan's description 'YES' or 'NO'
enabled 'YES' means the plan is available for use by the optimizer depending on accepted status 'YES' or 'NO'
fixed 'YES' means the baseline is not evolved. A fixed plan takes precedence over a non-fixed plan 30 char
plan_name SQL Plan's name 30 char
conn / as sysdba

set linesize 121
col name format a40
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'optimizer_capture%';

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE SCOPE=MEMORY;

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'optimizer_capture%';

GRANT select ON dba_sql_plan_baselines TO uwclass;

desc dba_sql_plan_baselines

SELECT sql_handle, autopurge
FROM dba_sql_plan_baselines;

set serveroutput on

DECLARE
 i NATURAL;
BEGIN
  i := dbms_spm.alter_sql_plan_baseline('SYS_SQL_ffb0d0ba09dba808',
       attribute_name => 'autopurge', attribute_value => 'NO');
  dbms_output.put_line(i);
END;
/

SELECT sql_handle, autopurge
FROM dba_sql_plan_baselines;

DECLARE
 i NATURAL;
BEGIN
  i := dbms_spm.alter_sql_plan_baseline('SYS_SQL_ffb0d0ba09dba808',
       attribute_name => 'autopurge', attribute_value => 'YES');

  dbms_output.put_line(i);
END;
/
 
CANCEL_EVOLVE_TASK (new 12.1)
Cancels a currently executing evolve task dbms_spm.cancel_evolve_task(task_name IN VARCHAR2);
exec dbms_spm.cancel_evolve_task('UWEVOLVE');
 
CONFIGURE
Set configuration options for the SQL Management Base (SMB) as well as the maintenance of SQL plan baselines dbms_spm.configure(
parameter_name  IN VARCHAR2,
parameter_value IN NUMBER := NULL);

Parameter Name Range Default Value
PLAN_RETENTION_WEEKS 5 - 523 53
SPACE_BUDGET_PERCENT 1 - 50% 10
exec dbms_spm.configure('PLAN_RETENTION_WEEKS', 6);
 
CREATE_EVOLVE_TASK (new 12.1)
Creates an advisor task and sets its parameters

Overload 1
dbms_spm.create_evolve_task(
sql_handle  IN VARCHAR2 := NULL,
plan_name   IN VARCHAR2 := NULL,
time_limit  IN NUMBER   := DBMS_SPM.AUTO_LIMIT,
task_name   IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
DECLARE
 vVal VARCHAR2(30);
BEGIN
  vVal := dbms_spm.create_evolve_task(task_name=>'UWEVOLVE', description=>'Test Evolve Task');
  dbms_output.put_line(vVal);
END;
/

desc dba_advisor_tasks

col owner format a10
col advisor_name format a19
col description format a17

SELECT owner, description, advisor_name, created, last_modified, status
FROM dba_advisor_tasks
WHERE task_name = 'UWEVOLVE';

SELECT how_created, recommendation_count
FROM dba_advisor_tasks
WHERE task_name = 'UWEVOLVE';

desc dba_advisor_objects

SELECT owner, type, attr8
FROM dba_advisor_objects
WHERE task_name = 'UWEVOLVE';

DECLARE
 vVal VARCHAR2(30);
BEGIN
  vVal := dbms_spm.execute_evolve_task('UWEVOLVE', 'First Run', '1st Evolve Task Run');
  dbms_output.put_line(vVal);
END;
/

SELECT owner, task_id, execution_start, execution_end, status
FROM dba_advisor_log
WHERe task_name = 'UWEVOLVE';

exec dbms_spm.drop_evolve_task('UWEVOLVE');

SELECT COUNT(*)
FROM dba_advisor_objects
WHERE task_name = 'UWEVOLVE';
Overload 2 dbms_spm.create_evolve_task(
 
 
CREATE_STGTAB_BASELINE
Creates a staging table that will be used for the purpose of transporting SQL plan baselines from one system to another dbms_spm.create_stgtab_baseline(
table_name      IN VARCHAR2,
table_owner     IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL);
exec dbms_spm.create_stgtab_baseline('SPM_TAB', 'UWCLASS', 'UWDATA');

desc spm_tab

SELECT table_name
FROM user_tables;

desc SYS_KTFTB_SQL_TABLE

desc SYS_KTFTB_KEY_TABLE
 
DROP_EVOLVE_TASK (new 12.1)
Drops an evolved task dbms_spm.drop_evolve_task(task_name IN VARCHAR2);
See CREATE_EVOLVE_TASK Demo Above
 
DROP_MIGRATED_STORED_OUTLINE
Drop a stored outline created using MIGRATE_STORED_OUTLINE dbms_spm.migrate_stored_outline;
exec dbms_spm.drop_migrated_stored_outline;
 
DROP_SQL_PLAN_BASELINE
Drops a single plan, or all plans associated with a SQL statement dbms_spm.drop_sql_plan_baseline(
sql_handle IN VARCHAR2 := NULL,
plan_name  IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;
conn / as sysdba

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'optimizer_capture%';

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE SCOPE=MEMORY;

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'optimizer_capture%';

desc dba_sql_plan_baselines

SELECT sql_handle, fixed
FROM dba_sql_plan_baselines;

set serveroutput on

DECLARE
 i NATURAL;
BEGIN
  i := dbms_spm.drop_sql_plan_baseline('SYS_SQL_ffb0d0ba09dba808');
  dbms_output.put_line(i);
END;
/

SELECT sql_handle
FROM dba_sql_plan_baselines;
 
EVOLVE_SQL_PLAN_BASELINE
Evolves SQL plan baselines associated with one or more SQL statements

Overload 1
dbms_spm.evolve_sql_plan_baseline(
sql_handle IN VARCHAR2 := NULL,
plan_name  IN VARCHAR2 := NULL,
time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,
verify     IN VARCHAR2 := 'YES',
commit     IN VARCHAR2 := 'YES')
RETURN CLOB;
set linesize 121

-- dbms_workload_repository.create_baseline here

SELECT sql_handle
FROM dba_sql_plan_baselines;

SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SYS_SQL_fb8901c5d313048b'));

set serveroutput on

SELECT dbms_spm.evolve_sql_plan_baseline('SYS_SQL_fb8901c5d313048b')
FROM dual;
Overload 2 dbms_spm.evolve_sql_plan_baseline(
plan_list  IN DBMS_SPM.NAME_LIST,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT
,
verify     IN VARCHAR2 := 'YES',
commit     IN VARCHAR2 := 'YES')
RETURN CLOB;
TBD
 
EXECUTE_EVOLVE_TASK (new 12.1)
Executes a previously created evolve task dbms_spm.execute_evolve_task(
task_name      IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
execution_desc IN VARCHAR2 := NULL)
RETURN VARCHAR2;
See CREATE_EVOLVE_TASK Demo Above
 
IMPLEMENT_EVOLVE_TASK (new 12.1)
Implements a plan based on the recommendation of an evolve task dbms_spm.implement_evolve_task(
task_name      IN VARCHAR2,
task_owner     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
force          IN BOOLEAN  := FALSE)
RETURN NUMBER;
exec dbms_spm.implement_evolve_task('UWEVOLVE');
 
INTERRUPT_EVOLVE_TASK (new 12.1)
Interrupts a currently executing evolve task dbms_spm.interrupt_evolve_task(task_name IN VARCHAR2);
exec dbms_spm.interrupt_evolve_task('UWEVOLVE');
 
LOAD_PLANS_FROM_CURSOR_CACHE
Loads one or more plans present in the cursor cache for a SQL statement

Overload 1
dbms_spm.load_plans_from_cursor_cache(
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2,
fixed           IN VARCHAR2 := 'NO',
enabled         IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
TBD
Overload 2 dbms_spm.load_plans_from_cursor_cache(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
fixed           IN VARCHAR2 := 'NO',
enabled         IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
TBD
Overload 3 dbms_spm.load_plans_from_cursor_cache(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_text        IN CLOB,
fixed           IN VARCHAR2 := 'NO',
enabled         IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
alter system flush shared_pool;

-- inefficient plan
SELECT /*+ NO_USE_NL(s,i) EVIL*/ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

3fub5wp0wwnad
6aptf1mfmdkx7


SELECT sql_id
FROM gv$sql
WHERE sql_text LIKE '%EVIL%';

-- more efficient plan
SELECT /*+ NO_USE_NL(s,i) GOOD*/ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id =i.srvr_id;

SELECT sql_id
FROM gv$sql
WHERE sql_text LIKE '%GOOD%';

2297mpruc77wp
2cry6yrdvrvvg


DECLARE
 i NATURAL;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache('3fub5wp0wwnad');
  i := dbms_spm.load_plans_from_cursor_cache('6aptf1mfmdkx7');
  i := dbms_spm.load_plans_from_cursor_cache('2297mpruc77wp');
  i := dbms_spm.load_plans_from_cursor_cache('2cry6yrdvrvvg');
END;
/

SELECT sql_handle, 'E'
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%EVIL%'
UNION
SELECT sql_handle, 'G'
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%GOOD%';

SYS_SQL_2421c46352e4a294 E
SYS_SQL_5ff768628a8142c5 G
SYS_SQL_c1ac82aaff233e4a G
SYS_SQL_eb7bc6820dc885a5 E


SELECT dbms_spm.evolve_sql_plan_baseline('SYS_SQL_eb7bc6820dc885a5')
FROM dual;
Overload 4 dbms_spm.load_plans_from_cursor_cache(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_handle      IN VARCHAR2,
fixed           IN VARCHAR2 := 'NO',
enabled         IN VARCHAR2 := 'YES')
RETURN PLS_INTEGER;
TBD
 
LOAD_PLANS_FROM_SQLSET
Loads plans stored in a SQL tuning set (STS) into SQL plan baselines dbms_spm.load_plans_from_sqlset(
sqlset_name  IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
fixed        IN VARCHAR2 := 'NO',
enabled      IN VARCHAR2 := 'YES'
commit_rows  IN NUMBER := 1000)
RETURN PLS_INTEGER;
TBD
 
MIGRATE_STORED_OUTLINE
Used to migrate stored outlines for one or more SQL statements to sql plan baselines in SMB

Overload 1
dbms_spm.migrate_stored_outline(
attribute_name  IN VARCHAR2,
attribute_value IN CLOB     := NULL,
fixed           IN VARCHAR2 := 'NO')
RETURN CLOB;

Attribute Name Values
ALL
CATEGORY
OUTLINE_NAME
SQL_TEXT
DECLARE
uw_outlines CLOB;
BEGIN
  uw_outlines := dbms_spm.migrate_stored_outline('ALL');
END;
/
Overload 2 dbms_spm.migrate_stored_outline(
outln_lst IN dbms_spm.name_list,
fixed     IN VARCHAR2 := 'NO')
RETURN CLOB;
TBD
 
PACK_STGTAB_BASELINE
Packs (exports) SQL plan baselines from SQL management base into a staging table dbms_spm.pack_stgtab_baseline(
table_name  IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
sql_handle  IN VARCHAR2 := NULL,
plan_name   IN VARCHAR2 := '%',
sql_text    IN CLOB     := '%',
creator     IN VARCHAR2 := NULL,
origin      IN VARCHAR2 := NULL,
enabled     IN VARCHAR2 := NULL,
accepted    IN VARCHAR2 := NULL,
fixed       IN VARCHAR2 := NULL,
module      IN VARCHAR2 := NULL,
action      IN VARCHAR2 := NULL)
RETURN NUMBER;
exec dbms_spm.create_stgtab_baseline('SPM_TAB', 'UWCLASS', 'UWDATA');

desc spm_tab

SELECT sql_handle, fixed
FROM dba_sql_plan_baselines;

SELECT COUNT(*)
FROM spm_tab;

DECLARE
 i POSITIVE;
BEGIN
  i := dbms_spm.pack_stgtab_baseline('SPM_TAB', 'UWCLASS', 'SYS_SQL_ffa1f1f91c5bca64');
END;
/

set linesize 121
col creator format a8
col origin format a15
col created format a30

SELECT sql_handle, creator, origin, created, optimizer_cost
FROM spm_tab;
 
REPORT_AUTO_EVOLVE_TASK (new 12.1)
Displays the results of an execution of an automatic evolve task dbms_spm.report_auto_evolve_task(
type           IN VARCHAR2 := 'TEXT',
level          IN VARCHAR2 := 'TYPICAL',
section        IN VARCHAR2 := 'ALL',
object_id      IN NUMBER   := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN CLOB;
DECLARE
 cVal CLOB;
BEGIN
  cVal := dbms_spm.report_auto_evolve_task;
  dbms_output.put_line(cVal);
END;
/
 
REPORT_EVOLVE_TASK (new 12.1)
Displays the results of an evolved task dbms_spm.report_evolve_task(
task_name      IN VARCHAR2,
type           IN VARCHAR2 := 'TEXT',
level          IN VARCHAR2 := 'TYPICAL',
section        IN VARCHAR2 := 'ALL',
object_id      IN NUMBER   := NULL,
task_owner     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN CLOB;
DECLARE
 cVal CLOB;
BEGIN
  cVal := dbms_spm.report_evolve_task('UWEVOLVE');
  dbms_output.put_line(cVal);
END;
/
 
RESET_EVOLVE_TASK (new 12.1)
Restarts an evolve task dbms_spm.reset_evolve_task(task_name IN VARCHAR2);
exec dbms_spm.reset_evolve_task('UWEVOLVE');
 
RESUME_EVOLVE_TASK (new 12.1)
Resume an evolve task dbms_spm.resume_evolve_task(task_name IN VARCHAR2);
exec dbms_spm.resume_evolve_task('UWEVOLVE');
 
SET_EVOLVE_TASK_PARAMETER (new 12.1)
Sets a parameter of an evolve task

Overload 1
dbms_spm.set_evolve_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
Overload 2 dbms_spm.set_evolve_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN VARCHAR2);
TBD
 
UNPACK_STGTAB_BASELINE
Unpacks (imports) SQL plan baselines from a staging table into SQL management base dbms_spm.unpack_stgtab_baseline(
table_name  IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
sql_handle  IN VARCHAR2 := NULL,
plan_name   IN VARCHAR2 := '%',
sql_text    IN CLOB     := '%',
creator     IN VARCHAR2 := NULL,
origin      IN VARCHAR2 := NULL,
enabled     IN VARCHAR2 := NULL,
accepted    IN VARCHAR2 := NULL,
fixed       IN VARCHAR2 := NULL,
module      IN VARCHAR2 := NULL,
action      IN VARCHAR2 := NULL)
RETURN NUMBER;
DECLARE
 i POSITIVE;
BEGIN
  i := dbms_spm.unpack_stgtab_baseline('SPM_TAB', 'UWCLASS', 'SYS_SQL_ffa1f1f91c5bca64');
END;
/

Related Topics
AWR
Explain Plan
DBMS_ADVISOR
DBMS_SPM_INTERNAL
DBMS_SQLTUNE
DBMS_XPLAN
Outlines
Packages