DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, August 16, 2014

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]”

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.