kksfbc child completion

今天在測試時，發現會話有kksfbc child completion等待事件，查mos發現為如下情況

（1）我的問題可能的情況

另外：如果單個SQL，每次執行都不能共享，非常高的version count（v$sql可以查到），非常快的cursor obsolete（_cursor_obsolete_threshold引數以及shared pool空間共同控制，v$sql中IS_OBSOLETE欄位可查為Y即被標記為可捨棄），根據下面Oracle C函式可推測，即硬解析時，很快找到cursor邊界，也會發生這個等待事件，我認為可以忽略，這種情況不常見。

kksfbc

kernel compile shared objects (cursor) find bound cursor

oracle 11.2.0.4 中 _cursor_obsolete_threshold引數值為1024

oracle 19c _cursor_obsolete_threshold引數值為8192

（2）bug的情況

Cause

The cause of this problem has been identified in
 - BATCH JOBS HANG WAITING ON 'KKSFBC CHILD COMPLETION'

It is caused by a session going into an infinite spin just after a wait for 'kksfbc child completion'. The spin can be identified in the call stack by the following routines: kksSearchChildList -> kkshgnc, where kksSearchChildList loops forever.

This problem can also lead to internal errors such as:

ORA-600 [kksSearchChildList1]
ORA-600 [kksSearchChildList2]
ORA-600 [kksSearchChildList3]
ORA-600 [kkshgnc-nextchild]

Solution

Please execute both the actions in Step A and in Step B as follows to resolve this issue.

A) As the patch for the initial bug introduces the problem described in unpublished Bug 8575528, download and apply   which superceeds the patch for Bug 6795880
Steps for obtaining the patch:

AND

B) Set the following instance parameter and restart the instance. Please make a note that this parameter is needed to enable the fix. Just applying Patch is not sufficient. When using an SPFILE, issue:

and restart the instance.

For PFILE usage, set following parameter in PFILE and restart the instance.

The parameter is only necessary when installing the one-off fix. When installing the 10.2.0.5 or 11.1.0.7 patch sets there is no need for this parameter.

Reference

ORA-600 [kkshgnc-nextchild] With Wait For [kksfbc child completion] (Doc ID 943989.1)