High Version Count with CURSOR_SHARING = SIMILAR or FORCE
(Doc ID 261020.1)
Last updated on FEBRUARY 14, 2019
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 and later
Oracle Database - Personal Edition - Version 18.104.22.168 and later
Oracle Database - Standard Edition - Version 22.214.171.124 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!