Oracle – OSD kill succeeded for process

Below error message can be found on Alert log file 

Process 0x0x970a9e428 appears to be hung in Auto SQL Tuning task

Current time = 15325698424, process death time = 1568426597

Attempting to kill process 0x0x960a7e152 with OS pid = 256158

OSD kill succeeded for process 0x0x960a7e152

Operational error (OSD kill succee...) detected in alert log…

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.

 

For instance, we have 2 workarounds .

increase the LOCAL_TIME_LIMIT parameter,

1. increase the LOCAL_TIME_LIMIT parameter

       



SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK' AND parameter_name = 'LOCAL_TIME_LIMIT';

       
 

 

PARAMETER_NAME PARAMETER_VALUE

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

LOCAL_TIME_LIMIT 1200

 

To increase 

 

       



BEGIN

DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK',
'LOCAL_TIME_LIMIT', 21600);
END;
/


       
 

 

       



SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK' AND parameter_name = 'LOCAL_TIME_LIMIT';

       
 

PARAMETER_NAME PARAMETER_VALUE

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

LOCAL_TIME_LIMIT 21600

2. Disable the automatic tuning process & manually execute 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 :

connect / as sysdba

 

       



BEGIN

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