8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Automatic SQL Tuning in Oracle Database 11g Release 2 (DBMS_AUTO_SQLTUNE)

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.

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

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>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.