Re: intermittent library cache pin/lock problems

On Mon, 12 Dec 2005 22:20:18 +0000 (UTC), "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>"Joel Garry" <joel-garry_at_home.com> wrote in message
>news:1134425403.977444.296680_at_o13g2000cwo.googlegroups.com...
>> >library cache load lock 3,225 2,895 37.78
>>>library cache pin 3,737 2,026 26.44
>>
>> Have you tried pinning things into the library cache? Look at
>> v$db_object_cache for larger objects (ie sharable_mem > 100000, type in
>> PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE) and kept = 'NO', or the
>> DBMS_SHARED_POOL.SIZES package.
>>
>> What can happen is that the shared pool gets fragmented, so it's
>> difficult to find contiguous memory for whatever needs to be loaded, so
>> you wait on those pins and locks. Made much worse by cursor_space_for
>> time because... well, read the doc!
>> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch132.htm#1015645
>>
>> If you make your shared pool big enough for all packages and all
>> cursors, there still might be memory leaks... I speculate you are
>> seeing a periodic garbage cleanup.
>
>The OP has cursor_space_for_time set to true - which,
>as Mladen pointed out, is possibly the problem. Effectively
>everything a user does will be pinned in the SGA until that
>session terminates.
>
>So, on the one hand, memory can easily become exhausted,
>and, on the other, your comment about garbage collection
>could be critical as everything that a user has done is pinned
>until they disconnect and then it is all cleared from memory -
>which could be a very aggressive thrash through the library
>cache, hammering various of the library cache latches and
>shared pool latch as objects are discarded.
>
>The trouble with cursor_space_for_time = true, is that it takes
>just one rogue process to interfere in the normal running of
>the system to cause massive memory problems. You need
>a virtually perfect implementation before it is safe to use this
>feature.
>

From my understanding the parameter (which, btw, at some point Oracle recommended to set) should keep all the objects for any (all sessions) until that session exits. And you're implying that either during garbage collection of such a session, or a general memory exhaustion could be causing this problem.

I think memory exhaustion should be ruled out, since there was plenty (~100M) of 'free' memory left over in the shared pool (and it's something we constantly monitor with scripts anyway). However, the garbage collection is interesting. How often do you think Oracle does that (I think you're implying on session disconnect), and could it be monitored what was affected during a clean up?

I also don't fully understand why the pin would even be needed for sql statements that appear to be frequently executed and use bind variables (i've seen a whole bunch of those for the sessions waiting for library cache pin/load lock)

How does one determine if the pool (library cache) is fragmented, or look for other degradation pointers? I feel there is too much 'magic' surrounding the shared pool. There isn't much description of 'internals' exactly what latch (children) are needed for what operation in the documentation.
.......
We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4 remove NSPAM to email Received on Mon Dec 12 2005 - 16:57:43 CST