Hung Auto SQL Tuning Task Messages

Comments Off
Posted in Uncategorized by Maria Gyurova @ Feb 10, 2012

 

Still in Oracle 11.2.0.3 may appear an alert in EM related to metric “Generic Operational Error”. Occasionally when running Automatic SQL Tuning the following messages may appear in the alert log:

Process 0x%p appears to be hung in Auto SQL Tuning task
 Current time = %u, death time = %u
 Attempting to kill process 0x%p with OS pid = %s
 OSD kill succeeded for process 0x%p

You can have a look at “How to Avoid or Prevent Hung Auto Tuning Task Messages [ID 1344499.1]” in Metalink and this post .

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:

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

 

The Automatic SQL Tuning :

SELECT   TASK_NAME, DESCRIPTION, STATUS, LAST_MODIFIED  FROM   DBA_ADVISOR_TASKS
 WHERE   task_name LIKE ‘%AUTO_SQL_TUNING_TASK%’;

 

is part of the Automated Maintenance Tasks together with Optimizer Statistics Gathering and Segment Advisor.  You can see this also in Oracle EM Grid Control->Server->Automated Maintenance Tasks.

 

 

 

 

I have a similar post but the error in the alert log is different.

 

Cheers,
Maria

 

See original