Re: v$sql - executions vs loads vs invalidations
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
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.. .