Results for “drop_sql_plan_directive”

6 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

SYS.DBMS_SPD is the Oracle-supplied PL/SQL package body that implements the SQL Plan Directive (SPD) management interface, part of the Automatic SQL Tuning infrastructure delivered with the Oracle Database 11g and 12c server. In Oracle E-Business Suite 12.1.1 and 12.2.2, the package is owned by SYS and is classified by the E-Business Suite Technical Reference Manual (ETRM) as an OTHER API, meaning it is not a documented EBS application programming interface but a database kernel package that the EBS technology stack depends upon. Its business function is to manage the directives the optimizer uses to decide when to gather additional statistics (dynamic sampling or column group creation) on behalf of SQL that repeatedly exhibits poor cardinality estimates. SQL Plan Directives are the persistent, dictionary-stored replacement for the older dynamic sampling hints and are generated by the automatic reoptimization loop and the SQL Tuning Advisor. This package provides the administration, transport, and preference layer around that loop. The ETRM records the object as an 11g-style wrapped package body; the wrapper obscures the source text but the documented callable surface is nine programs.

EBS relevance is indirect but material. The EBS 12.1.1 and 12.2.2 database tiers run the optimizer under Automatic SQL Tuning and, where licensed, the SQL Tuning Advisor and SQL Plan Management. Administrators occasionally invoke DBMS_SPD to inspect, disable, or carry SQL Plan Directives between environments, for example into a test clone or from development into production, so that the same optimizer directives that stabilized a plan in one environment are reproduced in another. The reference file name "dpo-S2T(Send_to_Approval).xlsx" appearing in the search that surfaced this object is unrelated to the package itself; it is a repository artifact. The package is referenced by two other packages according to the ETRM metadata.

Key Procedures and Functions

The ETRM documents nine programs. Parameter lists are deliberately omitted.

  • ALTER_SQL_PLAN_DIRECTIVE — Modifies an existing directive, for example to change its state (enabled/disabled) or to set the directive to a different plan-related action, without dropping and recreating it.
  • DROP_SQL_PLAN_DIRECTIVE — Removes one or more directives from the dictionary, optionally by directive identifier, owner, object, or the SQL statements associated with the directive.
  • FLUSH_SQL_PLAN_DIRECTIVE — Clears directives that are no longer needed, typically those whose associated SQL is no longer present or that have not recurred, reclaiming dictionary space.
  • CREATE_STGTAB_DIRECTIVE — Creates a staging table used to package directives for transport between databases; the staging table mirrors the directive and directive-metadata structures.
  • PACK_STGTAB_DIRECTIVE — Populates the staging table with the selected directives and their metadata, producing an exportable set.
  • UNPACK_STGTAB_DIRECTIVE — Reads a previously packed staging table and inserts the directives into the target database's dictionary.
  • SET_PREFS — Sets SPD preferences, most notably the automatic directive generation behavior and the directive capture/retention thresholds that govern whether SPDs are created or expired.
  • GET_PREFS — Returns the current values of the SPD preferences for inspection and diagnostics.
  • TRANSFER_SPD_FOR_DP — Transfers SQL Plan Directives associated with a Data Pump operation, allowing directives to be moved between databases or schemas as part of a data movement step.

Tables Accessed

The ETRM metadata for this object lists no EBS application tables accessed through APPS synonyms. DBMS_SPD operates exclusively on SYS-owned dictionary objects, chiefly the SQL Plan Directive data dictionary tables underlying the DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS views, together with the directive history/state tables and the staging tables created by CREATE_STGTAB_DIRECTIVE. Because the package reads and writes only these kernel objects, it is not a candidate for EBS table dependency analysis. Any staging table produced by CREATE_STGTAB_DIRECTIVE using the default naming is created in the caller's schema, so a DBA performing a transport should create it in an administrative schema rather than in an EBS application schema such as APPS.

Usage Notes

DBMS_SPD is ordinarily invoked directly from SQL*Plus or an equivalent DBA session, not from an EBS form or concurrent program. No EBS form or standard request is documented as calling it. Typical operations in an EBS 12.1.1/12.2.2 environment include:

  • Querying GET_PREFS to confirm the state of automatic SPD generation before and after an upgrade or patching activity, since the optimizer may create directives after a Statistics Gathering job runs.
  • Using SET_PREFS to disable automatic directive generation on systems where SPD-driven plan changes have caused regressions, a common reactive measure after an EBS patch or database upgrade.
  • Using ALTER_SQL_PLAN_DIRECTIVE to disable a specific directive without dropping it, preserving audit history.
  • Using CREATE_STGTAB_DIRECTIVE, PACK_STGTAB_DIRECTIVE, and UNPACK_STGTAB_DIRECTIVE to copy accepted directives from a certified source environment to a target, ensuring plan stability during a clone or migration.
  • Using DROP_SQL_PLAN_DIRECTIVE or FLUSH_SQL_PLAN_DIRECTIVE for cleanup, particularly after high-churn SQL workloads have accumulated stale directives.
  • Using TRANSFER_SPD_FOR_DP during export/import of a schema or table where directives should travel with the data.

Because the package is wrapped and SYS-owned, EBS customization is neither expected nor supported. Any automation should be built as external scripts executed by a DBA with SYSDBA or equivalent privileges, and changes should be recorded in the EBS change-control process. Callers should also be aware that two other packages reference DBMS_SPD; disabling or altering directives can therefore affect behavior elsewhere in the technology stack, so changes should be exercised first in a non-production clone.