Automated SQL Tuning - Displaying Automatic SQL Tuning Job Details

Problem
You have an Oracle Database 11g environment and want to determine if the Automatic SQL Tuning job is enabled and regularly running. If the job is enabled, you want to display other aspects, such as when it starts and how long it executes.

Solution
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;

The following output shows that there are three enabled automatic jobs running, one of which is the SQL Tuning Advisor:

CLIENT_NAME                       STATUS            CONSUMER_GROUP         WINDOW_GROUP

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

sql tuning advisor               ENABLED    ORA$AUTOTASK_SQL_GROUP       ORA$AT_WGRP_SQ

auto space advisor               ENABLED  ORA$AUTOTASK_SPACE_GROUP       ORA$AT_WGRP_SA

auto optimizer stats collection  ENABLED  ORA$AUTOTASK_STATS_GROUP       ORA$AT_WGRP_OS

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;

Here is some sample output:

TASK_NAME                          STATUS TO_CHAR(EXECUTION_END

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

SYS_AUTO_SQL_TUNING_TASK        COMPLETED       30-APR-11 06:00

SYS_AUTO_SQL_TUNING_TASK        COMPLETED       01-MAY-11 06:02

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

• Automatic SQL Tuning Advisor
• Automatic Segment Advisor
• Automatic Optimizer Statistics Collection

These tasks are automatically configured to run in maintenance windows. A maintenance window is a specified time and duration for the task to run. You can view the maintenance window details with this query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')

,sql_tune_advisor, optimizer_stats, segment_advisor

FROM dba_autotask_window_clients;

WINDOW_NAME      TO_CHAR(WINDOW_NEXT_TIME SQL_TUNE OPTIMIZE SEGMENT_

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

THURSDAY_WINDOW        28-APR-11 22:00:00  ENABLED  ENABLED  ENABLED

FRIDAY_WINDOW          29-APR-11 22:00:00  ENABLED  ENABLED  ENABLED

SATURDAY_WINDOW        30-APR-11 06:00:00  ENABLED  ENABLED  ENABLED

SUNDAY_WINDOW          01-MAY-11 06:00:00  ENABLED  ENABLED  ENABLED

There are several data dictionary views related to the automatically scheduled jobs. See Table 11-1 for descriptions of these views.