Re: intermittent library cache pin/lock problems

"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.
>
> jg
> --
> @home.com is bogus.
> SEO http://msnbc.msn.com/id/10415455/site/newsweek/
>

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.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005