http://www.orasql.com/blog/archives/2013/04/07/108.htm
直接摘过来了,暂时没时间处理,有兴趣的可以参考下!
故障时出现”KKSFBC CHILD COMPLETION”等待,同时导致session陷入无休止的自旋(spins)中,这种自旋(spins)发生在由堆栈调用(stack call)kksSearchChildList->kkshgnc陷入对kksSearchChildList函数的无限循环中,无法释放session,经分析确认由Oracle BUG(MOS ID 6795880.8)引起。该BUG由shared pool中sql软解析找寻合适的子游标时触发,导致ora_s001_xgp61进程消耗大量CPU,系统无法正常使用。
问题时段,ora_s001_xgp61进程消耗大量CPU:
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
6 ? 5757 oracle 178 20 17045M 8136K run 1311:48 101.32 101.14 ora_s001_xgp61
问题时段,kill session后系统CPU仍然很高,且sql id为1y2hq8fm88bzt,等待事件为kksfbc child completion,相关session信息如下:
565 HELIOS KILLED dllhost.exe 1y2hq8fm88bzt 2013-3-17 10:46:11 kksfbc child completion
以KKSFBC CHILD COMPLETION为关键字到MOS查询可以找到<Bug 6795880 – Session spins / OERI after ‘kksfbc child completion’ wait – superceded [ID 6795880.8]>,该Bug的症状为进程不断spin消耗大量CPU、出现’KKSFBC CHILD COMPLETION’等待事件,直接影响的版本有11.1.0.6、10.2.0.3和10.2.0.4。
该Bug是在发生’kksfbc child completion’等待事件后session陷入无休止的自旋(spins)中,这种自旋(spins)发生在由堆栈调用(stack call)kksSearchChildList->kkshgnc陷入对kksSearchChildList函数的无限循环中,session无法释放,且kill session无响应,最后只有在系统中kill -9相关进程。
由于当时ASH保留最早的为17号18点开始,’ kksfbc child completion’等待事件很轻微,且awr采集也是5秒一次。加上这种无限循环,在不断的重新调用自己,并没有等待,所以从ASH和AWR中未发现异常等待,各项数据显示正常。
鉴于该BUG触发的机率很小,且升级或者打补丁都对整个系统有较大的影响,如果下次再遇到某个oracle进程导致cpu很高,且从v$session视图中查看相关的等待事件为kksfbc child completion的问题,先使用oradebug将该进程的详细信息保存下来(后面附有oradebug使用方法),然后直接使用kill -9命令将该进程杀掉。
如果以后频繁出现这个问题,并确认是由这个bug引起,建议协调相关资源,系统中打上Patch 8575528,增加隐含参数"_cursor_features_enabled" = 10后并重启使fix生效,保证数据库的正常运行。
Description
This fix has been superseded by bug:8575528.A session may go into an infinite spin just after a waitfor 'kksfbc child completion'. The spin occurs with a stack including kksSearchChildList -> kkshgnc wherekksSearchChildList loops forever.This problem can also lead to internal error such as any ofORA-600 [kksSearchChildList1], ORA-600 [kksSearchChildList2]ORA-600 [kksSearchChildList3], ORA-600 [kkshgnc-nextchild]Or Trace dumps on kksSearchChildList4 .Note: This fix is disabled by default in 10g. To enable this fix you must explicitly set the following parameter for instance startup:
"_cursor_features_enabled" = 10
Fixed:This issue is fixed in
* 11.1.0.7 (Server Patch Set)
* 10.2.0.5 (Server Patch Set)
* 10.2.0.4 Patch 5 on Windows Platforms