Search Results set_auto_tuning_task_parameter




Overview
Explore in AI Chat

Overview

SYS.DBMS_AUTO_SQLTUNE is an Oracle-supplied PL/SQL package that exposes the Automatic SQL Tuning infrastructure to administrators and application code. Within Oracle E-Business Suite 12.1.1 and 12.2.2, it provides the programmatic interface for managing the automatic SQL tuning task maintained by the Automatic SQL Tuning Advisor. The advisor examines high-load SQL statements captured by the Automatic Workload Repository and produces tuning recommendations such as SQL profiles, index suggestions, and statistics refreshes.

The package is owned by SYS and reported as VALID in the EBS Technical Reference Manual (ETRM) for 12.2.2, where it is classified under the API classification OTHER. It is a database-tier component rather than an EBS application-tier API; EBS does not ship its own wrapper package for this functionality. The dependency metadata confirms that DBMS_AUTO_SQLTUNE builds on SYS.DBMS_ADVISOR, SYS.DBMS_SQLTUNE, and STANDARD, and that it is referenced by PUBLIC and by its own package body. No other EBS packages are documented as referencing it.

Key Procedures and Functions

  • EXECUTE_AUTO_TUNING_TASK — Runs the automatic SQL tuning task, either on demand or as part of a scheduled window. It drives the analysis of candidate SQL statements and produces tuning output for later review.
  • REPORT_AUTO_TUNING_TASK — Generates a textual report describing the results of an automatic SQL tuning task execution, including the findings and recommendations produced during the run.
  • SET_AUTO_TUNING_TASK_PARAMETER — Modifies configuration parameters that control the behavior of the automatic tuning task, such as execution scope, time limits, and result retention.

The ETRM metadata documents five procedures or functions in total for this package; the three above are the principal entry points used in EBS environments. Parameter lists are intentionally not reproduced here, as they are version-dependent and must be verified against the database release in use.

Tables Accessed

The ETRM dependency extract for this object records no tables referenced through APPS synonyms, which is consistent with its role as a SYS-owned database utility. Internally the package operates on the Automatic Workload Repository and SQL tuning repository structures, including the advisor task, finding, and recommendation tables that underpin DBMS_ADVISOR and DBMS_SQLTUNE. EBS application tables are not accessed directly by this package.

Usage Notes

DBMS_AUTO_SQLTUNE is normally invoked from the database tier rather than from EBS forms or concurrent programs. Typical invocations include direct calls from SQL*Plus or SQL Developer, inclusion in database scheduler jobs, and execution through the automatic maintenance window configured by the AutoTask. DBAs use it to force an immediate run of automatic SQL tuning outside the standard maintenance window, to retrieve a report of the last run, or to adjust task parameters such as the time limit per statement and the acceptance of SQL profiles.

Because EBS 12.1.1 and 12.2.2 both run on supported Oracle Database releases, the package is available in both environments, but its behavior is governed by database initialization parameters and the AutoTask configuration rather than by EBS profile options. The referenced-by list shows zero dependent EBS packages, so custom code that calls DBMS_AUTO_SQLTUNE should be treated as a direct database dependency and validated after database upgrades. Any customization should be limited to read-only reporting calls unless a controlled change process governs parameter changes, since automatic SQL tuning can alter execution plans across the EBS schema.