High Version Count with CURSOR_SHARING = SIMILAR or FORCE
(Doc ID 261020.1)
Last updated on NOVEMBER 07, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 9.0.1.0 and laterOracle Database - Personal Edition - Version 9.0.1.0 and later
Oracle Database - Standard Edition - Version 9.0.1.0 and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database 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.
Symptoms
- 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.
Changes
CURSOR_SHARING has been changed to SIMILAR or FORCE.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |