Oracle DBMS_SPM
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
 
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 DBMS_SYS_ERROR
DBMS_ADVISOR DBMS_SQLDIAG DBMS_XPLAN_LIB
DBMS_ASSERT DBMS_SQLTUNE DUAL
DBMS_LOB DBMS_SQLTUNE_INTERNAL PLITBLM
DBMS_SMB DBMS_SQLTUNE_UTIL1 PRVT_ADVISOR
DBMS_SMB_INTERNAL DBMS_SQLTUNE_UTIL2 SQL_PLAN_TABLE_TYPE
DBMS_SPM_INTERNAL DBMS_STANDARD XMLTYPE
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
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
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
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(
plan_list   IN dbms_spm.name_list,
time_limit  IN NUMBER   := dbms_spm.auto_limit,
task_name   IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
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_all_tables;

desc SYS_KTFTB_SQL_TABLE

desc SYS_KTFTB_KEY_TABLE
 
DROP_EVOLVE_TASK
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
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
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
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_AWR (new 18.1)
Load plans from AWR as SQL plan baselines. Can be used to load the SQL Management Base (SMB) with SQL plan baselines for a set of SQL statements using the plans from the AWR dbms_spm.load_plans_from_awr(
begin_snap   IN NUMBER,
end_snap     IN NUMBER,
basic_filter IN VARCHAR2 := NULL,
fixed        IN VARCHAR2 := 'NO',
enabled      IN VARCHAR2 := 'YES',
commit_rows  IN NUMBER   := 1000,
dbid         IN NUMBER   := NULL)
RETURN PLS_INTEGER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time) INST_START,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT, s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

DECLARE
 retVal PLS_INTEGER;
BEGIN
  retVal := dbms_spm.load_plans_from_awr(277, 289);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/
 
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
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
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
Restarts an evolve task dbms_spm.reset_evolve_task(task_name IN VARCHAR2);
exec dbms_spm.reset_evolve_task('UWEVOLVE');
 
RESUME_EVOLVE_TASK
Resume an evolve task dbms_spm.resume_evolve_task(task_name IN VARCHAR2);
exec dbms_spm.resume_evolve_task('UWEVOLVE');
 
SET_EVOLVE_TASK_PARAMETER
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
Built-in Functions
Built-in Packages
Explain Plan
DBMS_ADVISOR
DBMS_SPM_INTERNAL
DBMS_SQLTUNE
DBMS_XPLAN
Outlines
What's New In 12cR2
What's New In 18cR3