Oracle | DBMS

Oracle DBMS_SPD 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 SQL Plan Management Directives

API for managing SQL plan directives which are objects generated automatically by Oracle. For example, if Oracle detects that the single table cardinality estimated made by the optimizer is different from the actual number of rows returned when accessing the table, it will automatically create a directive to perform dynamic statistics for the table. When any SQL statement referencing the table is compiled, the optimizer will perform dynamic statistics for the table to get a more accurate estimate.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
SPD_RETENTION_WEEKS_DEFAULT	VARCHAR2(4)	'53'

Data Types TYPE ObjectElem IS RECORD( owner dbms_quoted_id, -- owner of the object object_name dbms_quoted_id, -- name of the object object_type VARCHAR2(6)); -- 'TABLE' TYPE ObjectTab IS TABLE OF ObjectElem;

Dependencies

DBA_SQL_PLAN_DIRECTIVES	DBMS_SMB	DBMS_STATS_INTERNAL
DBA_SQL_PLAN_DIR_OBJECTS	DBMS_SPD_INTERNAL	DBMS_SYS_ERROR
DBMS_ASSERT	DBMS_STANDARD	PLITBLM
DBMS_FEATURE_SPD	DBMS_STATS

Documented Yes

Exceptions

Error Code	Reason
ORA-13158	object_does_not_exist
ORA-13159	table_already_exists
ORA-19374	invalid_stgtab
ORA-28104	invalid_input
ORA-29304	tablespace_missing
ORA-38171	insufficient_privilege
ORA-44001	invalid_schema

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

The invoker must have the ADMINISTER SQL MANAGEMENT OBJECT privilege

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

Subprograms

ALTER_SQL_PLAN_DIRECTIVE	FLUSH_SQL_PLAN_DIRECTIVE	SET_PREFS
CREATE_STGTAB_DIRECTIVE	GET_PREFS	TRANSFER_SPD_FOR_DP
DROP_SQL_PLAN_DIRECTIVE	PACK_STGTAB_DIRECTIVE	UNPACK_STGTAB_DIRECTIVE

ALTER_SQL_PLAN_DIRECTIVE

Change attributes of a SQL Plan Directive

dbms_spd.alter_sql_plan_directive(

      directive_id    IN NUMBER,

      attribute_name  IN VARCHAR2,

      attribute_value IN VARCHAR2);

Attributes	Vaues	Description
AUTO_DROP	NO	Directive will not be automaticaly dropped
AUTO_DROP	YES	Automatically dropped if not used within the SPD_RETENTION_WEEKS value
STATE	HAS_STATS	The objects have stats
STATE	MISSING_STATS	The directive objects do not have current optimizer stats
STATE	NEW	Newly created directive
STATE	PERMANENT	A permanent directive

col directive_id format 99999999999999999999

      

      SELECT directive_id, type, state, auto_drop, last_used

      FROM dba_sql_plan_directives;

      

      exec dbms_spd.alter_sql_plan_directive(16162032791226711612, 'AUTO_DROP', YES');

      

      SELECT owner, object_name, subobject_name, object_type

      FROM dba_sql_plan_dir_objects

      WHERE directive_id = 16162032791226711612;

CREATE_STGTAB_DIRECTIVE

Creates a staging table to pack SQL Plan directives for export dbms_spd.create_stgtab_directive( table_name IN VARCHAR2, table_owner IN VARCHAR2 := user, tablespace_name IN VARCHAR2 := NULL);

exec dbms_spd.create_stgtab_directive('DIRECTIVE_STAGING', USER, 'SYSTEM');

      

      SELECT object_name, object_type

      FROM user_objects

      WHERE created > SYSDATE-10/1440;

      

      desc directive_staging

      

      -- pack SH schema directives

      DECLARE

       packing_list dbms_spd.objecttab := dbms_spd.ObjectTab();

       dir_no       NUMBER;

      BEGIN

        packing_list.extend(1);

        packing_list(1).owner := 'SH';          -- schema name

        packing_list(1).object_name := NULL;    -- all tables in SH

        packing_list(1).object_type := 'TABLE'; -- type of object

      

        dir_no := dbms_spd.pack_stgtab_directive('DIRECTIVE_STAGING', USER, obj_list => packing_list);

        dbms_output.put_line('Directives = ' || TO_CHAR(dir_no));

      END;

      /

      

      -- pack directives relevant to sh.sales and sh.customers

      DECLARE

       packing_list dbms_spd.objecttab := dbms_spd.ObjectTab();

       dir_no       NUMBER;

      BEGIN

        packing_list.extend(2);

      

        -- sales table

        packing_list(1).owner := 'SH';

        packing_list(1).object_name := 'SALES';

        packing_list(1).object_type := 'TABLE';

       

        -- customers table

        packing_list(2).owner := 'SH';

        packing_list(2).object_name := 'CUSTOMERS';

        packing_list(2).object_type := 'TABLE';

       

        dir_no := dbms_spd.pack_stgtab_directive('DIRECTIVE_STAGING', USER, obj_list => packing_list);

        dbms_output.put_line('Directives Packed = ' || TO_CHAR(dir_no));

      END;

      /

      

      -- unpack all directives

      DECLARE

       dir_no NUMBER;

      BEGIN

        dir_no := dbms_spd.unpack_stgtab_directive('DIRECTIVE_STAGING');

        dbms_output.put_line('Unpacked ' || TO_CHAR(dir_no) || ' directives');

      END;

      /

      

      -- unpack directives relevant to objects in SH schema

      DECARE

       packing_list dbms_spd.objecttab := dbms_spd.ObjectTab();

       dir_cnt number;

      BEGIN

        packing_list.extend(1);

        packing_list(1).owner := 'SH'; -- schema name

        packing_list(1).object_name := null; -- all tables in SH

        packing_list(1).object_type := 'TABLE'; -- type of object

      

        dir_cnt := dbms_spd.unpack_stgtab_directive('mydirtab', obj_list => packing_list);

        dbms_output.put_line('Unpacked ' || TO_CHAR(dir_no) || ' directives');

      END;

      /

      

      -- unpack directives relevant to tables SALES and CUSTOMERS in SH schema

      DECLARE

       packing_list dbms_spd.objecttab := dbms_spd.ObjectTab();

       dir_cnt number;

      BEGIN

        packing_list.extend(2);

      

        -- sales table

        packing_list(1).owner := 'SH';

        packing_list(1).object_name := 'SALES';

        packing_list(1).object_type := 'TABLE';

       

        -- customers table

        packing_list(2).owner := 'SH';

        packing_list(2).object_name := 'CUSTOMERS';

        packing_list(2).object_type := 'TABLE';

       

        dir_no := dbms_spd.unpack_stgtab_directive('mydirtab', obj_list => packing_list);

        dbms_output.put_line('Unpacked ' || TO_CHAR(dir_no) || ' directives');

      END;

      /

      

      DROP TABLE directive_staging PURGE;

DROP_SQL_PLAN_DIRECTIVE

Drop an existing SQL Plan Directive dbms_spd.drop_sql_plan_directive(directive_id IN NUMBER);

col directive_id format 99999999999999999999

      

      SELECT directive_id, type, state, auto_drop, last_used

      FROM dba_sql_plan_directives;

      

      exec dbms_spd.drop_sql_plan_directive(16162032791226711612);

FLUSH_SQL_PLAN_DIRECTIVE

Manually flushes SQL Plan directives that has been automatically recorded in SGA memory while executing SQL statements dbms_spd.flush_sql_plan_directive;

exec dbms_spd.flush_sql_plan_directive;

GET_PREFS

Returns the retention value preferences for SQL Plan Directives dbms_spd.get_prefs(pname IN VARCHAR2) RETURN VARCHAR2;

-- from catfusrg.sql

SELECT dbms_spd.get_prefs('SPD_RETENTION_WEEKS')

      FROM dual;

      

      exec dbms_spd.set_prefs('SPD_RETENTION_WEEKS', '4');

      

      SELECT dbms_spd.get_prefs('SPD_RETENTION_WEEKS')

      FROM dual;

      

      exec dbms_spd.set_prefs('SPD_RETENTION_WEEKS', dbms_spd.SPD_RETENTION_WEEKS_DEFAULT);

      

      SELECT dbms_spd.get_prefs('SPD_RETENTION_WEEKS')

      FROM dual;

PACK_STGTAB_DIRECTIVE

Exports SQL Plan Directives into a staging table dbms_spd.pack_stgtab_directive( table_name IN VARCHAR2, table_owner IN VARCHAR2 := user, directive_id IN NUMBER := NULL, obj_list IN ObjectTab := NULL) RETURN NUMBER;

See CREATE_STGTAB_DIRECTIVE

SET_PREFS

Setting different preferences for SQL Plan Directives dbms_spd.set_prefs( pname IN VARCHAR2, pvalue IN VARCHAR2);

See GET_PREFS Demo Above

TRANSFER_SPD_FOR_DP

Undocumented: For internal use with DataPump dbms_spd.transfer_spd_for_dp( objlist_tabf IN VARCHAR2, dblinkf IN VARCHAR2, operation IN NUMBER);

TBD

UNPACK_STGTAB_DIRECTIVE

Unpacks (imports) SQL Plan Directives from a staging table dbms_spd.unpack_stgtab_directive( table_name IN VARCHAR2, table_owner IN VARCHAR2 := user, directive_id IN NUMBER := NULL, obj_list IN ObjectTab := NULL) RETURN NUMBER;

See CREATE_STGTAB_DIRECTIVE