Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!m73g2000cwd.googlegroups.com!not-for-mail From: "joel garry" Newsgroups: comp.databases.oracle.server Subject: Re: Connection between library cache latches and statistics? Date: 10 Oct 2006 15:45:13 -0700 Organization: http://groups.google.com Lines: 42 Message-ID: <1160520313.159376.261890@m73g2000cwd.googlegroups.com> References: <1160485686.081819.59340@k70g2000cwa.googlegroups.com> NNTP-Posting-Host: 67.116.125.178 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1160520319 20696 127.0.0.1 (10 Oct 2006 22:45:19 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 10 Oct 2006 22:45:19 +0000 (UTC) In-Reply-To: <1160485686.081819.59340@k70g2000cwa.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0),gzip(gfe),gzip(gfe) X-HTTP-Via: 1.0 ISA2K4-OC1 Complaints-To: groups-abuse@google.com Injection-Info: m73g2000cwd.googlegroups.com; posting-host=67.116.125.178; posting-account=YRNZ5wwAAAAg-yYjMFwy3FyWUbPiwNdq Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:276988 Dusan Bolek wrote: > I'm encountering a strange problem. We're suffering from terrible > library cache latches (30-50% of waits in statspack reports) when > table's stats being older than two business days. I do not understand > why, because the affected tables are only slowly changing and I can't > imagine any possible connection between stats too old and library latch > waits. > > Any ideas? > > Database: 9.2.0.7 EE 64bit Itanium > -- > Dusan Bolek No, but some wild speculation: Too many cursor invalidations cause pinning problems in your shared pool. Test: Up shared pool by 50% (or preferably, as much as you have cursors) and set cursor_space_for_time true. If that fixes it, unset csft and see if it was just some related shared pool issue. If it doesn't, curse me for wasting your time. :-) Looking at the 9208 patch list: Bug 4100130 - SGA memory leak on object statistics. Too bad they don't show exactly what that is all about, but at least it sounds like it could be what you are seeing. Throwing memory at it might put it off for a reasonable statistics gathering cycle. Bug 4339128 Heavy latch contention from queries against library cache views. Bug 4084154 - Enhancement: optimization to library cache load lock allocation code. Do you have any monitoring tools running? :-) jg -- @home.com is bogus. http://www.rollingstone.com/news/story/5934313/zappa_lives_on_in_lithuania