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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_SPD_INTERNAL
DBMS_SPM
DBMS_SPM_INTERNAL
DBMS_SQLTUNE
System Privileges
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx