Search Results get_prefs




Overview
Explore in AI Chat

Overview

SYS.DBMS_SPD is an Oracle Database-supplied, invoker-rights PL/SQL package that manages SQL Plan Directives (SPD). SQL Plan Directives are optimizer objects generated automatically by the Oracle server. For example, when the server detects that the single-table cardinality estimated by the optimizer diverges from the actual number of rows returned during table access, it creates a directive instructing the optimizer to perform dynamic sampling for that table. When any SQL statement referencing the table is subsequently compiled, the optimizer performs dynamic sampling to obtain a more accurate estimate.

Within Oracle E-Business Suite 12.1.1 and 12.2.2, DBMS_SPD is not an EBS-authored API. It is a SYS-owned database package exposed to the EBS schema through the standard administrative toolchain. It falls under the ETRM "OTHER" API classification because it is not an EBS product-specific business API, but it is nonetheless relevant to EBS environments where SQL statement performance and optimizer behavior directly affect concurrent processing, online form responsiveness, and reporting throughput. The package allows DBAs to inspect, modify, and transfer the directives that influence how the EBS cost-based optimizer samples data.

Key Procedures and Functions

The ETRM metadata documents nine subprograms. Note that most subprograms require the ADMINISTER SQL MANAGEMENT OBJECT privilege and commit the current transaction before performing the operation and committing again.

  • ALTER_SQL_PLAN_DIRECTIVE — Modifies attributes of an existing SQL Plan Directive, such as its enabled state or dynamic sampling behavior.
  • DROP_SQL_PLAN_DIRECTIVE — Removes a SQL Plan Directive from the system.
  • FLUSH_SQL_PLAN_DIRECTIVE — Removes directives that are no longer referenced, typically after plan changes make them obsolete.
  • CREATE_STGTAB_DIRECTIVE — Creates a staging table used to hold directives for transport between databases.
  • PACK_STGTAB_DIRECTIVE — Populates the staging table with directives for export or movement.
  • UNPACK_STGTAB_DIRECTIVE — Reads directives from the staging table and loads them into the target database.
  • SET_PREFS — Sets package-level preferences, most notably SPD_RETENTION_WEEKS, whose default value is 53 weeks.
  • GET_PREFS — Retrieves the current value of a package preference. Because the user searched for "get_prefs," it is worth emphasizing that this function returns configuration settings such as the retention period applied to automatically generated directives.
  • TRANSFER_SPD_FOR_DP — Transfers SPD information in support of data pump operations or related directive movement.

Tables Accessed

The package operates against the SQL Plan Directive infrastructure and exposes results through the dictionary views DBA_SQL_PLAN_DIRECTIVES, which lists all directives created in the system, and DBA_SQL_PLAN_DIR_OBJECTS, which displays the objects included in each directive. The package defines an ObjectElem record type containing owner, object_name, and object_type (with 'TABLE' as the supported object type) and an ObjectTab collection type, reflecting the set of database objects to which a directive applies. The ETRM metadata does not document any EBS application tables referenced through APPS synonyms. Persistent directive data is maintained in the SYSAUX tablespace rather than in EBS product schemas.

Usage Notes

DBMS_SPD is invoked through SQL*Plus, SQLcl, or custom administrative scripts by DBAs, not from EBS forms or concurrent programs. Typical scenarios include reviewing automatically generated directives in an EBS database, increasing or reducing SPD_RETENTION_WEEKS via SET_PREFS, disabling directives that cause excessive dynamic sampling, and copying directives between environments using the CREATE_PACK_UNPACK staging table workflow. Because the package commits the current transaction, it must never be called from within an open EBS transaction or from concurrent program code without careful consideration. It is referenced by two other packages per ETRM metadata, indicating indirect usage in the EBS toolchain.