Results for “dbms_sqltune”
50+ 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:
- CREATE_TUNING_TASK — Creates a SQL tuning task for a single statement, a SQL ID, or a SQL Tuning Set.
- SET_TUNING_TASK_PARAMETER — Modifies options such as time limit, scope, and test-execution behavior on an existing task.
- EXECUTE_TUNING_TASK — Runs the tuning task and produces recommendations.
- INTERRUPT_TUNING_TASK / CANCEL_TUNING_TASK / RESET_TUNING_TASK / RESUME_TUNING_TASK — Manage task lifecycle and control long-running or interrupted analyses.
- DROP_TUNING_TASK — Removes a tuning task and its results.
- REPORT_TUNING_TASK / SCRIPT_TUNING_TASK — Return the advisor findings as a textual report or as executable SQL scripts.
- IMPLEMENT_TUNING_TASK — Applies the accepted recommendations, including SQL profiles and plan changes.
- SCHEDULE_TUNING_TASK — Schedules the task through the DBMS_SCHEDULER framework.
- CREATE_SQL_PLAN_BASELINE — Establishes a SQL plan baseline to lock in a known-good execution plan.
- REPORT_AUTO_TUNING_TASK — Reports on results from Automatic SQL Tuning runs.
- CREATE_SQLSET / DROP_SQLSET / DELETE_SQLSET / LOAD_SQLSET / CAPTURE_CURSOR_CACHE_SQLSET / UPDATE_SQLSET — Build and maintain SQL Tuning Sets for batch analysis.
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.
-
SYNONYM: PUBLIC.DBMS_SQLTUNE 12.1.1
-
SYNONYM: PUBLIC.DBMS_SQLTUNE 12.2.2
-
PACKAGE: SYS.DBMS_SQLTUNE 12.2.2
-
PACKAGE: SYS.DBMS_SQLTUNE 12.1.1