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