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