查MOS发现如下bug:
Bug 22994542 Child cursor counts increase with TYPECHECK_MISMATCH with Unified Auditing
里面给出了一个workaround 是调整隐藏参数 _cursor_obsolete_threshold。
_cursor_obsolete_threshold目前是默认的设置1024。
但在v$sql里面发现，这个SQL的子游标大部分都是obsolete 状态，占用了3个多G。
问题
这些obsolete child cursor会保留多久，什么时候会被清除，或者说是否会优先于未过期的子游标被踢出共享内存？
--obsolete child cursor 正常不会马上被清理，当需要更多shared pool空间时才会被age out掉。还有一种就是flush shared pool 和purge都可以。
obsolete child cursor是会由于未过期的子游标被清理的。 这些占用的空间对shared pool有什么影响？
--正常会占用着shared pool的空间，当需要更多空间的时候会被清理，也许可能存在一些相关bug，但看起来案例比较少。 如果调整_cursor_obsolete_threshold，比如减小到200，obsolete状态的是否也会相应减少？
---_cursor_obsolete_threshold指的是保留子游标版本的个数。如你设置到200个，子游标的个数则最多保留200个不过期。 以下是一些文档说明，可以参考下： * We can execute the cursor many times without the child count increasing if the cursor can be shared. When we decide that the cursor is not shareable we create a new child. * Once the child count exceeds _cursor_obsolete_threshold it is *not* aged out immediately. It may still be in use by others and we use a "lazy" scheme where it will be aged out when we need more memory from the shared pool because we haven't got a large enough contiguous chunk to satisfy the request. * With reference to bug 12791981 , when the shared pool gets exhausted we try to free up memory but we cannot free handles that are referenced by other handles. In 12791981, the problem is that we could not free the obsolete child cursors because there was still a reference to them. The obsoleted parent and its child cursors will remain in the cache if they are still being executed and they will remain there until the shared pool comes under memory pressure where KGH (heap manager) will call into KGL (library cache) to request that KGL release some memory. Explicitly flushing the shared pool will also release the memory but may have a detrimental affect on the instance as other objects will be released and will need to be reloaded. The obsoleted cursors can be purged using dbms_shared_pool.purge() but you would need the fix for bug 14127231 to be in place first.
