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
SET_AUTO_TUNING_TASK_PARAMETER
REPORT_AUTO_TUNING_TASK
Related articles.
SQL Tuning Advisor (10g)
Automatic SQL Tuning in Oracle Database 11g Release 1
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.
CONN / AS SYSDBA
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(50);
BEGIN
l_return := DBMS_AUTO_SQLTUNE.execute_auto_tuning_task;
DBMS_OUTPUT.put_line(l_return);
END;
/
EXEC_63842
PL/SQL procedure successfully completed.
SQL>
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.
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
AND parameter_name = ‘ACCEPT_SQL_PROFILES’;
PARAMETER_NAME PARAMETER_VALUE
—————————— ——————————
ACCEPT_SQL_PROFILES FALSE
SQL>
— Pre 11.2.0.2
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
parameter => ‘ACCEPT_SQL_PROFILES’,
value => ‘TRUE’);
END;
/
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
AND parameter_name = ‘ACCEPT_SQL_PROFILES’;
PARAMETER_NAME PARAMETER_VALUE
—————————— ——————————
ACCEPT_SQL_PROFILES TRUE
SQL>
— 11.2.0.2 Onward
BEGIN
DBMS_AUTO_SQLTUNE.set_auto_tuning_task_parameter(
parameter => ‘ACCEPT_SQL_PROFILES’,
value => ‘FALSE’);
END;
/
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
AND parameter_name = ‘ACCEPT_SQL_PROFILES’;
PARAMETER_NAME PARAMETER_VALUE
—————————— ——————————
ACCEPT_SQL_PROFILES FALSE
SQL>
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.
SET LONG 1000000
SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task
FROM dual;
REPORT_AUTO_TUNING_TASK
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 32
Current Execution : EXEC_63842
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
REPORT_AUTO_TUNING_TASK
——————————————————————————–
Completion Status : COMPLETED
Started at : 06/09/2014 09:45:57
Completed at : 06/09/2014 09:46:06
Number of Candidate SQLs : 77
Cumulative Elapsed Time of SQL (s) : 5351
——————————————————————————-
There are no recommendations to improve the statements in the workload.
——————————————————————————-
SQL>
