Re: Connection between library cache latches and statistics?

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