| General Information |
| Purpose |
SQL Plan Management |
| 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_SPM_LIB |
SQLSET_ROW |
| DBMS_LOB |
DBMS_SQLTUNE |
PLITBLM |
| DBMS_OUTPUT |
DBMS_SQLTUNE_INTERNAL |
PRVT_ADVISOR |
| DBMS_SMB |
DBMS_STATS_LIB |
SQLSET_ROW |
| DBMS_SMB_INTERNAL |
DBMS_SYS_ERROR |
SQL_PLAN_TABLE_TYPE |
| DBMS_SPM |
DBMS_XPLAN_LIB |
XMLTYPE |
| DBMS_SPM_INTERNAL |
|
|
|
| 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 never purge |
'YES' or 'NO' |
| description |
Plan 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 |
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_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
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 |
| |
| 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 |
| 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; |
| |
| 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;
/ |
