Search Results dbms_spm_internal




Overview
Explore in AI Chat

Overview

DBMS_SPM_INTERNAL is an Oracle-supplied internal PL/SQL package body owned by the SYS schema. It is the private implementation layer beneath the publicly documented DBMS_SPM package, which provides the SQL Plan Management (SPM) framework used by the Oracle optimizer to capture, evolve, and control SQL execution plans through SQL plan baselines. Within Oracle E-Business Suite 12.1.1 and 12.2.2, the package operates transparently as part of the database kernel rather than as an EBS application object. Its principal business function is to maintain plan stability for the high-volume, repetitive SQL statements generated by EBS modules — such as Order Management, Purchasing, General Ledger, and the concurrent manager — so that a plan change caused by statistics refresh, optimizer parameter changes, or database upgrades does not cause unpredictable performance regressions. The object is reported with VALID status in the ETRM inventory for release 12.2.2 and depends on a broad set of SYS advisor, tuning, and SPM infrastructure components.

Key Procedures and Functions

The ETRM metadata for this package body documents no explicit procedures or functions, and it is classified under API classification OTHER. This is consistent with its nature: DBMS_SPM_INTERNAL exists only to support the external DBMS_SPM API and is not intended for direct invocation by DBAs, developers, or EBS concurrent programs. Its internal logic provides the implementation behind the DBMS_SPM entry points that customers use, including the loading of plans into baselines, the packing and unpacking of plan data into staging tables, the evolution and verification of candidate plans, the alteration and dropping of baselines, and the reporting of SPM configuration and baseline contents. Because no parameter lists are exposed in the documented metadata, no signatures are reproduced here. All functional capability is reached exclusively through DBMS_SPM.

Tables Accessed

The dependency list identifies the data structures this package body reads and writes. The primary repository is DBA_SQL_PLAN_BASELINES, which stores the accepted and unaccepted plan baselines themselves. Advisor framework tables are heavily referenced, including DBA_ADVISOR_TASKS, DBA_ADVISOR_OBJECTS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS, DBA_ADVISOR_EXECUTIONS, DBA_ADVISOR_EXEC_PARAMETERS, and DBA_ADVISOR_SQLSTATS; these underpin SPM's integration with the Automatic SQL Tuning Advisor, which proposes plans for evolution. The package also touches SQL$TEXT, SQLOBJ$, SQLOBJ$AUXDATA, WRI$_ADV_OBJECTS, SMB$CONFIG, and the DUAL and XMLAGG constructs used to assemble report and diagnostic output. SQLSET_ROW and SQL_PLAN_TABLE_TYPE supply the object types used to pass SQL sets and plan rows between internal routines. Through APPS synonyms the ETRM view records XMLAGG as the referenced table, reflecting the XML aggregation used in baseline reporting.

Usage Notes

DBMS_SPM_INTERNAL is never called directly. EBS administrators and DBAs interact with SQL Plan Management through DBMS_SPM, through Enterprise Manager, or through the SQL Tuning Advisor, and those products dispatch into this package body. The ETRM record states that it is not referenced by any database object, meaning no stored PL/SQL unit or EBS form references it by name; its callers are the Oracle kernel and the public DBMS_SPM wrapper. When troubleshooting EBS performance in 12.1.1 or 12.2.2, DBAs query DBA_SQL_PLAN_BASELINES and the SPM configuration to confirm that baselines are enabled and captured. Because the package is SYS-owned proprietary code, customization is not supported; any modification risks invalidating the object and breaking the optimizer's plan control. Patching should be left to standard Oracle database and EBS patching, and errors surfaced from this package generally indicate corruption in the SQL management base or advisor repository rather than an EBS application defect.