High Version Count with CURSOR_SHARING = SIMILAR or FORCE
Last updated on MAY 24, 2017
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 and later
Oracle Database - Personal Edition - Version 22.214.171.124 and later
Oracle Database - Standard Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
- Significant database time spent waiting for library cache latch
- High parse rates in AWR/Statspack reports
- High version counts in AWR/Statspack reports
- cursor_sharing = SIMILAR
- Intermittent database-wide slowdowns
- STATSPACK, AWR or V$SQLAREA shows a high version count on cursors that have bind replacement done due to CURSOR_SHARING=SIMILAR or FORCE.
- V$SQL_SHARED_CURSOR does not show any reason why this is happening.
- the number of children could keep on increasing until the shared pool is filled. In some cases, if the number gets past 1024 a new hash value is created for the next set of 1024.
High version counts can easily cause high contention for library cache latches. A process parsing a SQL statement with many versions (children cursors) will need to scan
through all these children while holding on to a library cache latch. This means that other processes needing the same latch will have to wait and can lead to significant database-wide performance degradation.
CURSOR_SHARING has been changed to SIMILAR or FORCE.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms