OSD kill succeeded for process ....in alert log

Problem Statement :

------------------------

Occasionally, when running the "SYS_AUTO_SQL_TUNING_TASK" warning messages such as the following may appear in the alert log:

 

Tue Jun 12 20:10:41 2018
Process 0x0x1751359070 appears to be hung in Auto SQL Tuning task
Current time = 1528798241, process death time = 1528798205
Attempting to kill process 0x0x1751359070 with OS pid = 12630
OSD kill succeeded for process 0x1751359070

….

Operational error (OSD kill succee...) detected in 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 skipped for process %p"

"OSD kill succeeded for process %p"

"OSD kill failed for process %p"

 

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.

 

Analysis:

----------

Workaround:   increase the LOCAL_TIME_LIMIT parameter, making sure  the  TIME_LIMIT parameter  is not set to a lower value.  (described in note also)

 

or

 

>  Disable the automatic tuning process - 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;

/

 

Reference : Doc ID 1344499.1