DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Sunday, August 31, 2014

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.

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>

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.