Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news-han1.dfn.de!news-stu1.dfn.de!news.belwue.de!news.uni-stuttgart.de!carbon.eu.sun.com!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: intermittent library cache pin/lock problems Date: Mon, 12 Dec 2005 22:20:18 +0000 (UTC) Organization: BT Openworld Lines: 63 Message-ID: References: <4399bc61.174954125@localhost><4399c352.176731265@localhost> <1134425403.977444.296680@o13g2000cwo.googlegroups.com> NNTP-Posting-Host: host86-131-2-178.range86-131.btcentralplus.com X-Trace: nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com 1134426018 2473 86.131.2.178 (12 Dec 2005 22:20:18 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Mon, 12 Dec 2005 22:20:18 +0000 (UTC) X-RFC2646: Format=Flowed; Original X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:257604 "Joel Garry" wrote in message news:1134425403.977444.296680@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