Search Results unpack_stgtab_baseline

Overview

SYS.DBMS_SPM is the Oracle-supplied PL/SQL package that exposes the SQL Plan Management (SPM) framework to the E-Business Suite environment. It provides the programmatic interface for managing SQL plan baselines, SQL profiles, and the SQL Management Base (SMB) stored in the SYSAUX tablespace. In EBS 12.1.1 and 12.2.2 the package is declared with AUTHID CURRENT_USER and is owned by SYS, with the APPS schema granted EXECUTE through a synonym so that concurrent programs, forms, and custom code can invoke it without qualifying the SYS schema.

The central business purpose is plan stability: DBMS_SPM lets administrators capture known-good execution plans, verify that the optimizer reproduces them after statistics refreshes, upgrades, or patch application, and retire plans that are no longer used. It also governs the space consumed by SQL management objects, protecting the SYSAUX tablespace from runaway growth.

The search term "time_limit" refers directly to a documented constant in the package specification. DBMS_SPM defines two special values used with the time_limit parameter of EVOLVE_SQL_PLAN_BASELINE and its task-based equivalents: NO_LIMIT (2147483647) and AUTO_LIMIT (2147483647 - 1). These constants allow a caller to let plan evolution run without a wall-clock cap or to defer to the automatic limit determined by the system.

Key Procedures and Functions

• CONFIGURE — Sets SMB configuration options, notably SPACE_BUDGET_PERCENT (1..50, default 10%) and PLAN_RETENTION_WEEKS (5..523, default one year and one week). A NULL value restores the system default.
• LOAD_PLANS_FROM_SQLSET — Loads plans into a baseline from a stored SQL Tuning Set.
• LOAD_PLANS_FROM_CURSOR_CACHE — Loads plans into a baseline directly from the shared pool cursor cache.
• ALTER_SQL_PLAN_BASELINE — Modifies attributes such as enabled, fixed, and autopurge on existing baselines.
• DROP_SQL_PLAN_BASELINE — Removes one or more baselines from the SMB.
• EVOLVE_SQL_PLAN_BASELINE — Verifies unaccepted plans against the baseline and admits those that perform better. This is the procedure whose time_limit parameter accepts the NO_LIMIT and AUTO_LIMIT constants.
• CREATE_STGTAB_BASELINE, PACK_STGTAB_BASELINE, UNPACK_STGTAB_BASELINE — Create a staging table and transport baselines between databases.
• MIGRATE_STORED_OUTLINE, DROP_MIGRATED_STORED_OUTLINE — Convert legacy stored outlines into SQL plan baselines and remove the migrated outlines.
• CREATE_EVOLVE_TASK, SET_EVOLVE_TASK_PARAMETER, EXECUTE_EVOLVE_TASK, INTERRUPT_EVOLVE_TASK, CANCEL_EVOLVE_TASK, RESET_EVOLVE_TASK, RESUME_EVOLVE_TASK, DROP_EVOLVE_TASK, REPORT_EVOLVE_TASK — The task-based evolution API, providing resumable, reportable plan verification as an alternative to the single-call EVOLVE_SQL_PLAN_BASELINE interface.

Tables Accessed

The package operates on the SPM dictionary objects owned by SYS in the SYSAUX tablespace, exposed to APPS through synonyms: the SQL Management Base tables that store plan baselines, SQL profiles, and their attributes; the evolve task metadata and result tables written by the CREATE_EVOLVE_TASK family; and the staging table created by CREATE_STGTAB_BASELINE. The ETRM metadata lists no application tables referenced directly, which is consistent with SPM being a database-level feature rather than an EBS application feature.

Usage Notes

DBMS_SPM is normally invoked by the EBS database administrator rather than by end users. Typical entry points include SQL*Plus or SQLcl scripts run as APPS, the Automatic SQL Tuning Advisor and SQL Tuning Set jobs in Enterprise Manager, and Oracle-supplied maintenance jobs that purge unused plans according to PLAN_RETENTION_WEEKS. EBS patch and upgrade runbooks frequently include CONFIGURE, LOAD_PLANS_FROM_CURSOR_CACHE, and EVOLVE_SQL_PLAN_BASELINE calls to preserve performance across an application of patches. Custom concurrent programs can call the package if the APPS user is granted EXECUTE.

Because it is owned by SYS and declared AUTHID CURRENT_USER, privileges and any referenced objects are resolved in the caller's schema; custom wrappers should therefore be created and executed as APPS. Set time_limit to DBMS_SPM.NO_LIMIT only for controlled verification windows, since unconstrained evolution can consume significant CPU.