Re: v$sql - executions vs loads vs invalidations

From: Gerry Miller <gerry_at_millerandbowman.com>
Date: Wed, 02 May 2012 08:43:14 +1000
Message-ID: <4FA06702.2050207_at_millerandbowman.com>



 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.

Regards

Gerry

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.. . Regards
GregG  

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 01 2012 - 17:43:14 CDT

Original text of this message