Oracle DBMS_SPM Demos

Oracle DBMS_SPM Version 11.2.0.3

General Information

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_INTERNAL	DBMS_XPLAN_LIB
DBMS_ASSERT	DBMS_SPM_LIB	PLITBLM
DBMS_LOB	DBMS_SQLTUNE	PRVT_ADVISOR
DBMS_OUTPUT	DBMS_SQLTUNE_INTERNAL	SQLSET_ROW
DBMS_SMB	DBMS_STATS_LIB	SQL_PLAN_TABLE_TYPE
DBMS_SMB_INTERNAL	DBMS_SYS_ERROR	XMLTYPE

First Available

11.1.0.6

Security Model

Owned by SYS: Execute is granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/dbmsspm.sql

Subprograms

ALTER_SQL_PLAN_BASELINE	DROP_SQL_PLAN_BASELINE	MIGRATE_STORED_OUTLINE
CONFIGURE	EVOLVE_SQL_PLAN_BASELINE	PACK_STGTAB_BASELINE
CREATE_STGTAB_BASELINE	LOAD_PLANS_FROM_CURSOR_CACHE	UNPACK_STGTAB_BASELINE
DROP_MIGRATED_STORED_OUTLINE	LOAD_PLANS_FROM_SQLSET

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=BOTH;

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;
/

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_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_MIGRATED_STORED_OUTLINE (new 11.2.0.1)

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

LOAD_PLANS_FROM_CURSOR_CACHE

Loads one or more plans present in the cursor cache for a SQL statement

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 2

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

Overload 3

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 4

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

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 (new 11.2.0.1)

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