How to enable and disable the auto task default job in Oracle

Disable and Enable Auto task Job in Oracle

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.

Following jobs is configured default by Oracle:
Automatic Optimizer Statistics Collection- Gathers stale or missing statistics
Automatic Segment Advisor – Identifies segments that reorganized to save space
Automatic SQL Tuning Advisor – Tune high load SQL

Disable all three jobs, you can used following command:

For Disable:
EXEC DBMS_AUTO_TASK_ADMIN.disable;

For Enable:
EXEC DBMS_AUTO_TASK_ADMIN.enable;

Disable one by one follow following commands:

1. Check the enabled job present in oracle database.

SQL> SELECT client_name, status FROM dba_autotask_client;

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

2. Disable the following 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);

PL/SQL procedure successfully completed.

3. Check the status again for auto task jobs.

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

4. Enable the auto task 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);

PL/SQL procedure successfully completed.