Results for “set_tuning_task_parameter”

8 results




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

Overview

DBMS_SQLTUNE is the Oracle-supplied PL/SQL package that implements the SQL Tuning Advisor, SQL Access Advisor, SQL Plan Management, and SQL Tuning Set infrastructure within the database kernel. In Oracle E-Business Suite 12.1.1 and 12.2.2, the package body is owned by SYS and is wrapped, meaning the source is obfuscated and cannot be read directly. It is classified in the ETRM repository under the "OTHER" API classification, reflecting that it is a database-tier utility rather than an EBS application schema object.

Within the EBS context, DBMS_SQLTUNE provides the engine used by the Automatic SQL Tuning job of the Automatic Maintenance Tasks framework, the SQL Tuning Advisor accessed through Oracle Enterprise Manager and the ADDM/AWR toolchain, and the SQL Plan Baseline mechanism that stabilizes execution plans for critical EBS SQL statements. The package is heavily referenced — the ETRM metadata records that it is referenced by 29 other database packages, underscoring its role as a foundational dependency across tuning, diagnostics, and plan-management workflows.

Key Procedures and Functions

The ETRM repository documents 83 procedures and functions for this package. The most significant include:

Tables Accessed

The ETRM metadata records XMLAGG as the table reference, resolved through APPS synonyms. XMLAGG is in fact an SQL aggregate function rather than a physical tuning table; its appearance in the metadata reflects how DBMS_SQLTUNE constructs XML-formatted advisor output and reports. The substantive repository tables — SQL tuning tasks, SQL Tuning Sets, SQL profiles, and plan baselines — are stored in the SYS-owned dictionary (for example, the SQL tuning and SQLSET data dictionary views), which sit behind the SYS schema rather than EBS application tables.

Usage Notes

DBMS_SQLTUNE is normally invoked indirectly rather than from forms. The most common paths within EBS 12.1.1 and 12.2.2 are: the Automatic SQL Tuning maintenance task, which runs DBMS_SQLTUNE internals on a nightly window; Oracle Enterprise Manager's SQL Tuning Advisor and SQL Plan Control pages; and custom DBA scripts or concurrent programs that call the package directly to create, execute, and report tuning tasks on high-load EBS SQL.

Because the body is owned by SYS and wrapped, direct modification is neither supported nor possible. DBAs interact with it through the documented PL/SQL API, through the DBMS_SQLTUNE package specification, or through EM. When custom code executes this package from the APPS schema, the necessary SYS grants (typically granted to the DBA role) must be in place. Given that 29 packages reference it, DBAs should treat changes to tuning parameters, SQL profiles, and plan baselines as high-impact operations, testing in a cloned environment before applying them to a production EBS instance.