Auto Sql Tuning Task 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.
Mar 28, 2019 Oracle Database - Enterprise Edition - Version 11.1.0.6 and later: How to Avoid or Prevent 'Process 0x%p appears to be hung in Auto SQL Tuning task' Messages How to Avoid or Prevent 'Process 0x%p appears to be hung in Auto SQL Tuning task' Messages. Automatic SQL Tuning in Oracle Database 11g Release 1; Automatic SQL Tuning in Oracle Database 11g Release 2 (DBMSAUTOSQLTUNE) Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2) Overview. In its normal mode the query optimizer needs to.
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:
Classics. Fiction Audiobooks. Cook book recipe pages download. Others.
1. Check the enabled job present in oracle database
SQL> SELECT client_name, status FROM dba_autotask_client;
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);
3utools error unable to restore idevice(-14). Jan 12, 2020 Fix 3utools Error Unable To Restore iDevice(-10) or (-2) iPhone/iPad/iPad hey welcome to back an other video today i'm going show you how to fix iphone resto.
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
Oracle Stop Sys_auto_sql_tuning_task
PL/SQL procedure successfully completed.
3. Check the status again for auto task jobs
SQL> SELECT client_name, status FROM dba_autotask_client;
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.
Performance Tuning Oracle Sql
In 11.2.0.3 database , we get alert in EM related to metric “Generic Operational Error” or 'Generic Operational Error Status '. Occasionally when running Automatic SQL Tuning the following messages may appear in the alert log:
Error-
These messages indicate that an auto kill of a 'hung'/long running tuning task has taken place.
This is a protective measure purely to avoid the task from over-running its time limit because of a single task and protects a the system from harm caused by such over-running.
Since this is an expected activity to prevent over-running there is no fix as such.
Instead as a workaround, you could:
FIX 1 :Give the task more time to complete (the following example would set the per statement timeout to 6 hours (21600 seconds)):
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 21600);
END;
/
Note
If you increase the per-statement time limit (LOCAL_TIME_LIMIT) then you need to stay within the bounds of the time limit for the entire task (TIME_LIMIT). The duration of the TIME_LIMIT parameter must be at least equal or greater than the LOCAL_TIME_LIMIT. When the maintenance window closes the SQL Tuning Advisor is stopped.
FIX 2 :
Disable the automatic tuning process and the messages will not appear anymore (though obviously the auto tuning will also no longer occur - you could manually execute the job as desired later).
To disable the job:
connect / as sysdba
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
To re-enable in future:
connect / as sysdba
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
Source :
How to Avoid or Prevent 'Process 0x%p appears to be hung in Auto SQL Tuning task' Messages (Doc ID 1344499.1)