High version count

From: amit poddar <amit.poddar_at_yale.edu>
Date: Tue, 14 Nov 2006 11:08:41 -0500
Message-ID: <4559EA09.7090202@yale.edu>

Hi folks,

Following is an extract from metalink bug: 5406524

"- A session has to load row cache data using dictionary SQL        


- The cursor for this gets closed but is cached        

- The cursors heap 6 gets flushed (by flush shared_pool in the           

   test but could be from natural shared pool flushing over time)        


- The LOCK on it is then marked BROKEN        

- Some new session issues a hard parse which needs to load           

   some row cache data so issues the same dictionary SQL statement 
.           


- *It cannot use the child with the missing heap 6 and it cannot           

  replace that child as it has a broken lock on it so a  new version 
gets created. .         *


- This cycle over time can lead to multiple versions           of the 

cursor with all "N" in V$SQL_SHARED_CURSOR. "

 From this it seems that a new version of sql is created whenever heap 6 of a sql is flushed out of the shared pool and the kgllock is broken.

My question is this also true when a cursor is invalidated. (i.e. a new version is created instead of recreating the existing invalid child)

thanks
amit

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 14 2006 - 10:08:41 CST