OSD kill succeeded for process

环境：oracle 12.2 单机rac + redhat 6.9

em13c报错：

在以下时间/行号处的 /home/u01/app/oracle/diag/rdbms/db6/db61/alert/log.xml 中检测到操作错误 (osd kill succee...): sun mar 15 23:23:40 2020/67198。

alert log:

2020-03-15t23:23:40.022325+08:00
process 0x0x403c7de50 appears to be hung in auto sql tuning task
current time = 1584285819, process death time = 1584285804
attempting to kill process 0x0x403c7de50 with os pid = 309067
osd kill succeeded for process 0x403c7de50

错误原因：
这个问题发生在sys_auto_sql_tuning_task运行时，自动kill了 hung或者长时间运行（long running）的进程任务，防止系统超负荷运行（over-running ）。

解决办法：
给sys_auto_sql_tuning_task任何更多的运行时间，默认是1200秒（20分钟），可以修改成6个小时（21600秒）。
    
select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'sys_auto_sql_tuning_task'
and parameter_name = 'local_time_limit';


begin
dbms_sqltune.set_tuning_task_parameter('sys_auto_sql_tuning_task', 'local_time_limit', 21600);
end;
/

error at line 1:
ora-13647: setting of parameter local_time_limit is disallowed during task
execution.
ora-06512: at "sys.prvt_advisor", line 4848
ora-06512: at "sys.prvt_advisor", line 4809
ora-06512: at "sys.dbms_sys_error", line 86
ora-06512: at "sys.prvt_advisor", line 1094
ora-06512: at "sys.prvt_advisor", line 4699
ora-06512: at "sys.prvt_advisor", line 4827
ora-06512: at "sys.dbms_advisor", line 409
ora-06512: at "sys.dbms_sqltune", line 1150
ora-06512: at line 2


connect / as sysdba

begin
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => null,
window_name => null);
end;
/


sql> select status from dba_advisor_tasks where task_name = 'sys_auto_sql_tuning_task';

status
-----------
executing
 

sql> exec dbms_sqltune.cancel_tuning_task('sys_auto_sql_tuning_task');

pl/sql procedure successfully completed.
 

sql> select status from dba_advisor_tasks where task_name = 'sys_auto_sql_tuning_task';

status
-----------
cancelled
 

sql> begin
  dbms_sqltune.set_tuning_task_parameter('sys_auto_sql_tuning_task', 'local_time_limit', 21600);
  end;
  /



begin
dbms_auto_task_admin.enable(
client_name => 'sql tuning advisor',
operation => null,
window_name => null);
end;
/
 


sql> select parameter_name,parameter_value from dba_advisor_parameters where task_name='sys_auto_sql_tuning_task';



select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'sys_auto_sql_tuning_task'
and parameter_name = 'local_time_limit';

 

参考mos

doc id 1344499.1  
doc id 1597819.1