Home > DBA > Hung Auto SQL Tuning Task Messages

Hung Auto SQL Tuning Task Messages

 

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, process 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

 

Advertisements
Categories: DBA
  1. April 3, 2012 at 9:16 am

    By default, automatic SQL tuning runs for at most one hour. It is not usually enough. Especially, if the database has a lot of “bad” SQL. If I enable AST, I increase the limit for the auto tuning task to 6h (10PM-4AM) and limit each SQL to 5-10 minutes (default is 30 min.). Sometimes, with default values, the job might hang for the 1h with just 2 SQL queries.

    BEGIN
    DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
    parameter => ‘TIME_LIMIT’, value => 21600); ‘LOCAL_TIME_LIMIT’, value => 600); <– 10 minutes
    END;
    /

  2. April 3, 2012 at 9:32 am

    BEGIN
    DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
    parameter => ‘TIME_LIMIT’, value => 21600); ‘LOCAL_TIME_LIMIT’, value => 600); <– 10 minutes
    END;
    /

    Somehow the syntax got messed up.

    • April 3, 2012 at 9:34 am

      BEGIN
      DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
      parameter => ‘TIME_LIMIT’, value => 21600); ‘LOCAL_TIME_LIMIT’, value => 600); <– 10 minutes
      END;
      /

      • April 3, 2012 at 9:55 am

        Hi Julian,

        Thanks for the shared input. Always interested to see what other guys practice.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: