HUNG IN AUTO SQL TUNING TASK

Scenario:

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 :

Sat Aug 16 07:10:54 2014
Process 0x0x85865e800 appears to be hung in Auto SQL Tuning task
Current time = 1408187453, process death time = 1408187402
Attempting to kill process 0x0x85865e800 with OS pid = 2289
OSD kill succeeded for process 0x85865e800
Sat Aug 16 07:13:00 2014

Solution:

The explanation is that the AUTO SQL TUNING TASK has been over-running and as a protective measure it is auto killed. As thus, there is no fix for this and the solution is to disable this job and eventually manually execute it when

needed. Here is how to do that:

To Disable the job:

BEGIN
   DBMS_AUTO_TASK_ADMIN.DISABLE(
   client_name => ‘sql tuning advisor’,
   operation => NULL,
   window_name => NULL);
 END;
 /


To Enable it when needed.

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


Reference:

“How to Avoid or Prevent Hung Auto Tuning Task Messages [ID 1344499.1]”