Search Results interrupt_tuning_task

Overview

SYS.DBMS_SQLTUNE is the core PL/SQL package underpinning the Automatic SQL Tuning and SQL Tuning Advisor infrastructure in Oracle Database, and it is fully present and valid in Oracle E-Business Suite 12.1.1 and 12.2.2. Within the EBS technical stack, the package provides the programmatic surface through which SQL statements are analyzed for performance regressions, access path inefficiencies, stale or missing statistics, and suboptimal plan choices. It is the entry point that EBS administrators and DBA teams use to create, schedule, execute, and report on tuning tasks against the high-volume SQL generated by concurrent managers, forms sessions, and interface programs.

The package is owned by SYS and classified under the ETRM API classification OTHER. It exposes 83 documented procedures and functions and is referenced by 29 other packages, placing it at the center of the database's advisor framework. Its principal semantic dependencies include DBMS_ADVISOR, DBMS_SPM, DBMS_SQLPA, DBMS_SQLDIAG, DBMS_SQL_MONITOR, and the workload repository objects WRI$_ADV_SQLTUNE, WRI$_REPT_SQLDETAIL, WRI$_REPT_SQLMONITOR, and WRI$_REPT_SQLT.

Key Procedures and Functions

The documented subprograms fall into four functional groups:

• Tuning task lifecycle: CREATE_TUNING_TASK, SET_TUNING_TASK_PARAMETER, EXECUTE_TUNING_TASK, INTERRUPT_TUNING_TASK, CANCEL_TUNING_TASK, RESET_TUNING_TASK, RESUME_TUNING_TASK, DROP_TUNING_TASK, and SCHEDULE_TUNING_TASK manage the creation, configuration, execution, interruption, restart, and removal of SQL tuning tasks.
• Reporting and output: REPORT_TUNING_TASK renders advisor findings in text, HTML, or XML; SCRIPT_TUNING_TASK emits the executable recommendations; REPORT_AUTO_TUNING_TASK summarizes results produced by the automated tuning job.
• Plan management: CREATE_SQL_PLAN_BASELINE and IMPLEMENT_TUNING_TASK apply accepted recommendations, including SQL plan baselines, back to the database.
• SQL Tuning Set management: CREATE_SQLSET, DROP_SQLSET, DELETE_SQLSET, LOAD_SQLSET, CAPTURE_CURSOR_CACHE_SQLSET, and UPDATE_SQLSET build and maintain SQL Tuning Sets from the cursor cache, AWR, or user-supplied sources.

Tables Accessed

The package persists tuning task definitions, findings, and recommendations in the workload repository, writing chiefly to WRI$_ADV_SQLTUNE. Reporting operations read WRI$_REPT_SQLDETAIL, WRI$_REPT_SQLMONITOR, and WRI$_REPT_SQLT. SQL Tuning Set membership and bind data are exposed through the data dictionary views ALL_SQLSET_BINDS, DBA_SQLSET_BINDS, and USER_SQLSET_BINDS, while historical bind information is read from DBA_HIST_SQLBIND. In EBS, the package references XMLAGG (via an APPS synonym) to assemble XML report fragments.

Usage Notes

DBMS_SQLTUNE is not invoked directly by EBS forms or standard concurrent programs. It is called indirectly through the Automatic SQL Tuning job configured via DBMS_AUTO_SQLTUNE, through Oracle Enterprise Manager's SQL Tuning Advisor pages, and through the DBMS_SQLPA SQL Performance Analyzer workflow. EBS DBAs typically invoke it from SQL*Plus or a custom concurrent program when investigating specific high-load SQL identified in DBA_HIST_SQLSTAT. Because task execution is resource-intensive, tuning tasks should be scheduled during low-activity windows and scoped to discrete SQL IDs or SQL Tuning Sets rather than run against an entire cursor cache.