Disable and Enable Auto task Job for 11g and 12c version in Oracle

Disable and Enable Auto task Job for 11g and 12c version 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.

Advertisements

One thought on “Disable and Enable Auto task Job for 11g and 12c version in Oracle

  1. Pingback: Disable and Enable Auto task Job for 11g and 12c version in Oracle | Dinesh Ram Kali.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s