kksfbc child completion

今天，偶然发现一数据库某个oracle进程占用大量的CPU，非大数据量，仅64条记录，没有触发器，逻辑读写很低，查询等待，发现

SQL> select s.sid,sw.event,sw.wait_time,sw.state 
  2   from v$session s,v$process p,v$session_wait sw
  3   where s.PADDR = p.ADDR
  4   and sw.SID = s.sid
  5   and p.SPID=237780;

       SID EVENT
---------- ----------------------------------------------------------------
 WAIT_TIME STATE
---------- -------------------
       503 kksfbc child completion
         5 WAITED KNOWN TIME

有一种说法是bug，1、Bug 6795880，相关的doc id为：6795880.8

<Bug 6795880 – Session spins / OERI after ‘kksfbc child completion’ wait – superceded [ID 6795880.8]>
我metalink到期了。没法查了。

Bug的描述是在发生’kksfbc child completion’等待事件后会话陷入无休止的自旋(spins)中，这种自旋(spins)发生在由堆栈调用(stack call)kksSearchChildList->kkshgnc陷入对kksSearchChildList函数的无限循环中。

直接影响版本11.1.0.6、10.2.0.3和10.2.0.4。

A session may go into an infinite spin just after a wait for 'kksfbc child completion'. The spin occurs with a stack including kksSearchChildList -> kkshgnc where kksSearchChildList loops forever. This problem can also lead to internal error such as any of ORA-600 [kksSearchChildList1], ORA-600 [kksSearchChildList2] ORA-600 [kksSearchChildList3], ORA-600 [kkshgnc-nextchild] Note: Fixes for this bug in 10g and 11gR1 are disabled by default. To enable this fix you must explicitly set the following parameter for instance startup: "_cursor_features_enabled" = 10

过了一会，又变成另外一种等待

SQL> select event,wait_time ,state from v$session_wait where sid=503;

EVENT                                                             WAIT_TIME
---------------------------------------------------------------- ----------
STATE
-------------------
ksdxexeotherwait                                                         -1
WAITED SHORT TIME

 有人说，这也是bug，由new child listener process forked  引起

Bug 4518443 is fixed in 10.2.0.3

- OR -

Apply Patch 4518443  for the problem (if a patch is available)

- OR -

As a workaround, the following parameter can be added to listener.ora
SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF
Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file.

For example, if the listener name is LISTENER (default), the parameter would be:
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

This will prevent the listener from registering against ONS (Oracle Notification Services), which is the area affected by bug:4518443. For more information on ONS, please refer to eg. the Oracle10g Release 2 documentation ("Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide").

Please note, adding SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> to listener.ora file on RAC, will mean that FAN (fast application notification) will not be possible. See Note 220970.1 RAC: Frequently Asked Questions for further information on FAN

 

session不会自动释放连接，及时在库一层kill掉，仍然占用CPU，只好在kill -9 237780 在操作系统层杀掉，释放CPU。

继续关注，如果再次或者频繁出现，或许真是bug，需要打补丁了。

 

过程中总结的：

SQL> oradebug setospid 3723390

SQL> oradebug unlimit;
Statement processed.

SQL> oradebug short_stack;
SQL> oradebug dump processstate 10;
Statement processed.

SQL> oradebug dump systemstate 266;
Statement processed.
awk -f ass109.awk /oracle/admin/budget/udump/budget_ora_791256.trc

sqlplus -prelim '/ as sysdba'

 

补充一句，

ftp://updates.oracle.com/8575528/p8575528_10.2.0.4_AIX64-5L.zip
下载8575528的补丁，然后修改参数"_cursor_features_enabled" = 10