Enable and Disable the Auto Task default job in Oracle DBA_AUTOTASK

Enable and Disable the Auto Task default job in Oracle DBA_AUTOTASK_CLIENT

11g onwards..

When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window.

Each job is assigned a job name that is generated at runtime. All automated maintenance task job names begin with ORA$AT.

For example, the job for the Automatic Segment Advisor might be called ORA$AT_SQ_SQL_SW_xxxx

NOTE:

The module name "DBMS_SCHEDULER" and action names starting with "ORA$AT" can for instance be queried via v$sql.

Automated database maintenance tasks is used by oracle to auto tune the SQL Queries, stale stats gather and space advisory. Some time this jobs change the execution plan and caused performance issue.

Auto optimizer stats collection - Automatic Optimizer Statistics Collection which gathers stale or missing statistics for all schema objects.

Auto space advisor- Automatic Segment Advisor Identifies segments that could be reorganized to save space

Sql tuning advisor - Automatic SQL Tuning Advisor Identifies and attempts to tune high load SQL

Query to check the Jobs:

select client_name, status from DBA_AUTOTASK_CLIENT;

Disabling the Jobs:

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

SQL> SELECT client_name, status FROM dba_autotask_client;

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

Enabling the Jobs:

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

Select from DBA_AUTOTASK_CLIENT is slow.

SQL> SET TIMING ON FEED ON

SQL> SELECT client_name FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME

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

auto space advisor

auto optimizer stats collection

sql tuning advisor

3 rows selected.

Elapsed: 00:01:10.19

Purge DBMS_SCHEDULER logs and gathering Dictionary Statistics

SQL> EXEC DBMS_SCHEDULER.PURGE_LOG();

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;