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.
Disable and Enable Auto task Job for 11g and 12c version in Oracle
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 Enable:
Disable one by one follow following commands:
1. Check the enabled job present in oracle database
2. Disable the following jobs
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
4. Enable the auto task jobs:
Sys_auto_sql_tuning_task Schedule Tonight
The Automatic SQL Tuning Task (SYSAUTOSQLTUNINGTASK) has been over-running, therefore it has auto killed to protect the system from harm caused by such over-running.This is a protective measure purely to avoid the task from over-running its time limit because of a single task and protects the system from harm caused by such over-running. Like any other SQL Tuning Advisor task, you can also execute the automatic tuning task SYSAUTOSQLTUNINGTASK using the EXECUTETUNINGTASK API. The SQL Tuning Advisor will perform the same analysis and actions as it would when run automatically. BEGIN DBMSSQLTUNE.settuningtaskparameter( taskname = 'SYSAUTOSQLTUNINGTASK', parameter = 'ACCEPTSQLPROFILES', value = 'TRUE'); END; /. The REPORTAUTOTUNINGTASK function of the DBMSSQLTUNE package returns a CLOB containing a report from the specified automatic tuning task.
SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
Sys_auto_sql_tuning_task Schedule TemplateSQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
Sys_auto_sql_tuning_task Schedule 2017PL/SQL procedure successfully completed.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |