Enabling Automated SQL Tuning in Oracle 11g

To identify if Automatic SQL Tuning job is enabled and regularly running. Use the following query to determine if any Automatic SQL Tuning jobs are enabled:

SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client ORDER BY client_name;

 If the Automatic SQL Tuning job is enabled, you will see something like this:

 SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                                  ENABLED
sql tuning advisor                                                    ENABLED

Run the following query to view the last several times the Automatic SQL Tuning Advisor job has run:

SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')

FROM dba_advisor_executions

WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'

ORDER BY execution_end;

TASK_NAME                      STATUS      TO_CHAR(EXECUTION_END,'DD-MON-YYHH24:MI')

------------------------------ ----------- ---------------------------------------------------------------------------

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   05-JUN-16 06:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   06-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   07-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   08-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   09-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   10-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   11-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   12-JUN-16 06:03

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   13-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   14-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   15-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   16-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   17-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   18-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   19-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   20-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   21-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   22-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   23-JUN-16 22:05

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   24-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   25-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   26-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   27-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   28-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   29-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   30-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   01-JUL-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   02-JUL-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   03-JUL-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   04-JUL-16 22:01

You will get the last execution date-time and status of Automatic SQL Tuning Task (SYS_AUTO_TUNING_TASK) or you will get the message No rows selected,

which obviously means that the Automatic SQL Tuning is enabled but the job has not been running.

How Does Automatic SQL Tuning Work?

When you create a database in Oracle Database 11g or higher, Oracle automatically implements three automatic maintenance jobs:

1 Automatic SQL Tuning Advisor

2 Automatic Segment Advisor

3 Automatic Optimizer Statistics Collection

How to View the Results of SQL Tuning Advisor:

SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

SQL> SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

How to Enable or DISABLE Automatic SQL Tuning

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;