Disable and Enable Auto task Job in Oracle ~ Blog for oracle RAC Goldengate Dataguard ASM database

Hello Friends,

In this post we will discuss about how to 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.

Below 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 Below command:

For Disable:

EXEC DBMS_AUTO_TASK_ADMIN.disable;

For Enable:

EXEC DBMS_AUTO_TASK_ADMIN.enable;

Disable one by one follow Below 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 Below 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.

Disable and Enable Auto task Job in Oracle

Related Post