Disable and Enable Auto task Job for 11g, 12c & 19c 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.
Pingback: Disable and Enable Auto task Job for 11g and 12c version in Oracle | Dinesh Ram Kali.