Re: Help: large "cache buffers lru chain", large performace degradation

"Nagarajan" <xml_naga_at_yahoo.com> wrote in message news:b2ea4548.0301121517.ea09223_at_posting.google.com...
>[snip]
> ORACLE instance started.
> Total System Global Area 603534648 bytes
> Fixed Size 52424 bytes
> Variable Size 533813360 bytes
> Database Buffers 67108864 bytes
> Redo Buffers 2560000 bytes

> [snip]

> i dropped the tables and recreated them with inittrans 5 and
> freelist groups 10. and presto, the buffer waits vanished but
> the latch free returned for "39" which is "library cache"!
> and lots of enqueue waits.
>

Yup, with a 500MB+ Shared Pool, I would quite confidently expect quite a bit of library cache latch contention (especially as compared with the size of your buffer cache).

More shared pool memory is not always better, you know. In fact, quite the opposite sometimes: the bigger it gets, the more it will fragment. And your users have to hold the library cache latch for longer and longer as they search for a free bit of library cache in which to store their execution plans. And since the library cache latch serializes access to the library cache, all your users start to queue up behind each other.

Your best bet is to take your report.txt and visit www.oraperf.com, feed it to Anjo Kolk's tried and trusted analysis scripts, and see what he suggests. But for starters, I'd be reducing the shared_pool_size by half. Just to test and see if it makes any difference.

Regards
HJR Received on Sun Jan 12 2003 - 20:23:34 CST