CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SPM
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsspm.sql
First Availability 11.1
Constants
Name Data Type Value
AUTO_LIMIT INTEGER 2147483647 - 1
NO_LIMIT INTEGER 2147483647
Dependencies
DBA_SQL_PLAN_BASELINES DBMS_SYS_ERROR SMB$CONFIG
DBMS_OUTPUT DBMS_XPLAN SQL$TEXT
DBMS_SPM_LIB DBMS_XPLAN_LIB SQLOBJ$
DBMS_SQLTUNE DUAL SQLSET_ROW
DBMS_SQLTUNE_INTERNAL GV$SQL_PLAN_STATISTICS SQL_PLAN_TABLE_TYPE
DBMS_SQLTUNE_UTIL0 PLITBLM XMLTYPE
DBMS_STATS_LIB PRVT_ADVISOR  
Defined Data Type TYPE name_list IS TABLE OF VARCHAR2(30);
Security Model Execute is granted to PUBLIC
 
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 Values
autopurge 'YES' means the plan is purged if it is not used for a time period. 'NO' means it is never purged.

'YES' or 'NO'

description Plan description 'YES' or 'NO'
enabled 'YES' means the plan is available for use by the optimizer. It may or may not be used depending on accepted status. 'YES' or 'NO'
fixed 'YES' means the SQL plan baseline is not evolved over time. A fixed plan takes precedence over a non-fixed plan. 30 char
plan_name Name of the plan 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
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_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=BOTH;

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

Overload 1
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
 
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;
 
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_SQLTUNE
DBMS_XPLAN
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [128 users online]    © 2010 psoug.org