Databases Reference
In-Depth Information
Notice that the automatic SQL tuning job uses as its input the high resource statements found in the
AWR. Also, the job can be configured to automatically accept SQL profiles (more on this later).
Verifying Automatic Jobs Running
You can check on the status of the automatic SQL tuning job via this query:
SELECT client_name, status, consumer_group
FROM dba_autotask_client
ORDER BY client_name;
Here is some sample output showing that there are three automatically configured jobs:
CLIENT_NAME STATUS CONSUMER_GROUP
----------------------------------- ---------- -------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP
These tasks are automatically configured to run in regularly scheduled 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;
Here's a snippet of the output for this example:
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
Viewing Automatic SQL Tuning Job Advice
Now that you've established that the automatic SQL tuning job is running, you can view the advice it
generates via SQL*Plus, as follows:
SQL> SET LINESIZE 80 PAGESIZE 0 LONG 100000
SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
