Sunday, April 3, 2016

HUNG IN AUTO SQL TUNING TASK

Issue-
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)

No comments: