Automatic SQL Tuning

Automatic SQL Tuning

Automatic SQL Tuning is a new feature of Oracle 11g which utilizes the SQL Tuning Advisor (introduced in 10g) to create sql profiles for the queries executed on the database. This tuning task runs every night as part of the daily maintenance window.

Below are the steps carried by the automatic sql tuning process every night

Step 1: Analyzes the Automatic workload repository (AWR) to identify the top sql statements during different time periods in the past week.

Step 2: Assigns weight to each sql statement identified from different time periods and starts working from the top sql. And in the mean time, it invokes the sql tuning advisor to tune the previously identified sql statements.

Step 3: The sql tuning advisor creates a sql profile which are stored in sql management base.

Step 4: After thorough verification of the sql profile, Automatic sql tuning adds it to the sql plan baseline.

You can control the sql profile implementation and tuning from automatic maintenance tasks configuration page on the enterprise manager. DBMS_SQLTUNE package is used to control the automatic sql tuning from the command line

BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'LOCAL_TIME_LIMIT', 900);
END;

PL/SQL procedure successfully completed.

SQL>

Where LOCAL_TIME_LIMIT is the parameter to control the maximum time spent per SQL during the tuning activity. The value 900 means a maximum of 15 minutes.

MAX_SQL_PROFILES_PER_EXEC is the parameter to control the maximum number of sql profiles which can be implemented per execution. From our example, the maximum number of sql profile implementation per execution is 10.

BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'MAX_SQL_PROFILES_PER_EXEC', 10);
END;

PL/SQL procedure successfully completed.

SQL>

By default, the Automatic SQL Tuning implements the beneficial sql profiles after verification. To disable the automatic implementation, ACCEPT_SQL_PROFILES parameter should be set to FALSE as shown below

BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'FALSE');
END;

PL/SQL procedure successfully completed.

SQL>


The Automatic sql tuning can be disabled by disabling the sql tuning advisor using DBMS_AUTO_TASK_ADMIN package.

BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

PL/SQL procedure successfully completed.

SQL>

Some times we may need to disable the automatic sql tuning for a particular day. For example, if we want to disable the automatic sql tuning on thursday then we need to specify the window name.

BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;

PL/SQL procedure successfully completed.

SQL>


Similarly, to enable back the automatic sql tuning on Thursday

BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'THURSDAY_WINDOW');
END;

PL/SQL procedure successfully completed.

SQL>

To check the automatic sql tuning executions query DBA_ADVISOR_EXECUTIONS data dictionary table.



SQL> column execution_name format a10
SQL> column owner format a20
SQL> column advisor_name format a30
SQL>
SQL> select execution_name,status,owner,advisor_name from dba_advisor_executions where advisor_name = 'SQL Tuning Advisor';

EXECUTION_ STATUS OWNER ADVISOR_NAME
---------- ----------- -------------------- ------------------------------
EXEC_17 COMPLETED SYSTEM SQL Tuning Advisor
EXEC_23 COMPLETED SYS SQL Tuning Advisor
EXEC_62 COMPLETED SYS SQL Tuning Advisor

SQL>


From the above list we can generate a report of the execution using REPORT_AUTO_TUNING_TASK procedure.