Oracle Database 11g Release 2 (11.2.0.2 onward) has some minor changes to the Automatic SQL Tuning feature introduced on Oracle 11g Release 1. In the previous release, reports and amendments to the automatic tuning task parameters was performed using the DBMS_SQLTUNE package. From 11.2.0.2 onward, this should be done using the DBMS_AUTO_SQLTUNE package, which requires the DBA role.

Note. The top-level enabling and disabling of the admin task is still done using the DBMS_AUTO_TASK_ADMIN package, described here.

EXECUTE_AUTO_TUNING_TASK

The EXECUTE_AUTO_TUNING_TASK procedure and function manually initiate the SYS_AUTO_SQL_TUNING_TASK task. The only difference between the two is the function returns the task name, which is useful if you don't specify one, as it returns the system generated name.

The EXECUTE_AUTO_TUNING_TASK procedure and function can only be called when logged on as SYS.

SET_AUTO_TUNING_TASK_PARAMETER

The SET_AUTO_TUNING_TASK_PARAMETER procedures are overloads that accept both string and numeric parameter values. These replace DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER as the preferred way to amend the parameters for the SYS_AUTO_SQL_TUNING_TASK task. The parameters that can be amended are listed here.

The following example shows both the original (DBMS_SQLTUNE) and preferred method (DBMS_AUTO_SQLTUNE) of changing the parameters for the SYS_AUTO_SQL_TUNING_TASK task.

REPORT_AUTO_TUNING_TASK

The REPORT_AUTO_TUNING_TASK function replaces DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK as the preferred way to get the results of the SYS_AUTO_SQL_TUNING_TASK task. Calling it with no parameters shows the results for the latest execution of the task.