Re: v$sql - executions vs loads vs invalidations

  • From: Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx>
  • To: GG <grzegorzof@xxxxxxxxxx>
  • Date: Wed, 02 May 2012 08:43:14 +1000

 Yes, the reason for multiple subpools is to avoid library cache latch or
lock contention and this is the main reason I have chosen to put off the
modification of _kghdsidx_count.  I am quite surprised that there has been
virtually no such contention during this period.
Also, there are not a huge number of non-shared cursors but rather a number
of cursors that have been loaded a huge number of times and apparently never
executed.  I fully expected the CURSOR_SHARING=FORCE to reduce if not
eliminate the ORA-04031 errors and at least the number of SQL reloads but it
did neither and I suspect that the high number of  reloads is due to these
statements I mention. 



GG wrote: W dniu 2012-05-01 14:37, Gerry Miller pisze: Hi GG 

These cursors don't have a high version count; there is only one of each, 
but  it has been loaded thousands of times. 

and it said that ALL of the ORA_04031 errors occurred in subpool 2.  Until
then I was sceptical about subpool imbalance being the cause but now I am
notso sure. 
Subpool imbalance maybe caused by bug as well, You're saying there is a huge
number of non-shared cursors even when setting cursor_sharing to force .
I've got feeling we are missing something important here .
As a workaround You can use single subpool BUT first consult that with
Oraclesupport and dont blame me if any other issues appear .

 alter system set "_kghdsidx_count"=1 scope=spfile;
- This requires a database restart, and may cause some performance impact as
latch waits may increase.

Proper way for troubleshooting such issues is when You find out which
allocations makes imbalance and do some MOS
search for related bugs or kick some developers a.. .



Other related posts: