Oracle | DBMS

Oracle DBMS_SPM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_SYS_ERROR
DBMS_ADVISOR	DBMS_SPM_LIB	DBMS_XPLAN_LIB
DBMS_ASSERT	DBMS_SQLDIAG	DUAL
DBMS_AUTO_INDEX_INTERNAL	DBMS_SQLTUNE	PLITBLM
DBMS_AUTO_TASK_ADMIN	DBMS_SQLTUNE_INTERNAL	PRVT_ADVISOR
DBMS_LOB	DBMS_SQLTUNE_UTIL1	SQL_PLAN_TABLE_TYPE
DBMS_SMB	DBMS_SQLTUNE_UTIL2	XMLTYPE
DBMS_SMB_INTERNAL	DBMS_STANDARD

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

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsspm.sql

Subprograms

ACCEPT_SQL_PLAN_BASELINE	DROP_SQL_PLAN_BASELINE	MIGRATE_STORED_OUTLINE
ALTER_SQL_PLAN_BASELINE	EVOLVE_SQL_PLAN_BASELINE	PACK_STGTAB_BASELINE
CANCEL_EVOLVE_TASK	EXECUTE_EVOLVE_TASK	REPORT_AUTO_EVOLVE_TASK
CONFIGURE	IMPLEMENT_EVOLVE_TASK	REPORT_EVOLVE_TASK
CREATE_EVOLVE_TASK	INTERRUPT_EVOLVE_TASK	RESET_EVOLVE_TASK
CREATE_STGTAB_BASELINE	LOAD_PLANS_FROM_AWR	RESUME_EVOLVE_TASK
DROP_EVOLVE_TASK	LOAD_PLANS_FROM_CURSOR_CACHE	SET_EVOLVE_TASK_PARAMETER
DROP_MIGRATED_STORED_OUTLINE	LOAD_PLANS_FROM_SQLSET	UNPACK_STGTAB_BASELINE

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
AUTO_CAPTURE_ACTION	dbmsspm.sql	none
AUTO_CAPTURE_MODULE	dbmsspm.sql	none
AUTO_CAPTURE_PARSING_SCHEMA_NAME	dbmsspm.sql	none
AUTO_CAPTURE_SQL_TEXT	dbmsspm.sql	none
AUTO_SPM_EVOLVE_TASK	AUTO, OFF, ON	AUTO
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

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