OSD kill succeeded for process 0x919f4e918

OSD kill succeeded for process 0x919f4e918:

Sometimes you may notice that your alert log has following entries about a hung process and your monitoring system fires and alarm/alert for the same.

Alert Log:

Tue Aug 06 04:10:29 2019
Process 0x0x919f4e918 appears to be hung in Auto SQL Tuning task
Current time = 1565064629, process death time = 1565064600
Attempting to kill process 0x0x919f4e918 with OS pid = 262331
OSD kill succeeded for process 0x919f4e918
Tue Aug 06 04:10:42 2019

In above alert log entry, you see there is process death time limit and once current time of the process reaches to the defined process death time then that long running/hung process gets killed by OSD.


alert.xml

</msg>
<msg time='2019-08-06T04:10:29.069+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2489:3170456095' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='host01.example.com'
 host_addr='127.0.0.1' module='' pid='249175'>
 <txt>Process 0x0x919f4e918 appears to be hung in Auto SQL Tuning task
 </txt>
</msg>
<msg time='2019-08-06T04:10:29.069+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2493:1670650406' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='host01.example.com'
 host_addr='127.0.0.1' module='' pid='249175'>
 <txt>Current time = 1565064629, process death time = 1565064600
 </txt>
</msg>
<msg time='2019-08-06T04:10:29.069+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2504:2480300341' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='host01.example.com'
 host_addr='127.0.0.1' module='' pid='249175'>
 <txt>Attempting to kill process 0x0x919f4e918 with OS pid = 262331
 </txt>
</msg>

<msg time='2019-08-06T04:10:29.069+00:00' org_id='oracle' comp_id='rdbms'
 msg_id='kesaiKillProcess:2518:234340873' client_id='' type='ERROR'
 group='SQL_Tune' level='2' host_id='host01.example.com'
 host_addr='127.0.0.1' module='' pid='249175'>
 <txt>OSD kill succeeded for process 0x919f4e918
 </txt>
</msg>

Cause: 

It is expected to get this alert entry while "SYS_AUTO_SQL_TUNING_TASK" is running, since "SYS_AUTO_SQL_TUNING_TASK" runs in a defined maintenance window so OSD process invokes to kill the OS process that is running the "SYS_AUTO_SQL_TUNING_TASK" job longer than the maintenance window on the system to prevent or avoid the over-load on the system.

Solution:

If you would like the alert entry to be avoided and want this daily maintenance job(SQL Tuning job) to be completed then you may need to consider increasing the time limit for the "SYS_AUTO_SQL_TUNING_TASK" job on the system. Its default value is 1200 seconds.

SQL> col PARAMETER_VALUE for a30
SQL> SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name = 'LOCAL_TIME_LIMIT';  2    3    4

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ------------------------------
LOCAL_TIME_LIMIT               1200

SQL>

if you want to increase the maintenance/job time limit to 1 hour then run the following:

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

SQL> col PARAMETER_VALUE for a30
SQL> SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name = 'LOCAL_TIME_LIMIT';  2    3    4

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ------------------------------
LOCAL_TIME_LIMIT               3600

SQL>

Hope it would help someone !!

Stay Tuned/subscribe to this blog to get updates when new articles posted.

Twitter : @rajsoft8899