My Oracle Support Banner

QUERIES HAVING HUGE VERSION COUNTS WHEN CURSOR_SHARING=SIMILAR (Doc ID 731468.1)

Last updated on JANUARY 24, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

Queries having huge version counts when the statistics are not available for the objects involved in the sql statements but the dynamic sampling is used.

In 10g, dynamic sampling collects the histogram data also for the objects at the run time.

If the dynamic sampling is disabled, the version counts got reduced but the execution plan become very poor consuming more time.

The huge version counts consume lot of sharable memory and they got 4031 ssues. 

So the main cause of 4031 is the queries having huge version counts.

The bind variables are not used but cursor_sharing=similar is set and thats why literals have been replaced with system generated binds.

v$sql_shared_cursor shows huge no.of child cursors for the queries but no reason seen for the child cursors.

10046 trace for the problematic sqls showed the following:- 

oacflg=10 fl2=0300 => indicates that the literal passed is unsafe.

Changes

 

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!


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.