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

oraperf gives the following advice..

24% -> tune the "cache buffers chain latch" no_details 23% -> reduce data block contention check the objects that are inserted into that have enough freelists (more than 1 and it should be a prime number)
5% -> check system and user datafile, i did this using dbv and they are ok.
3% -> tune the library cache latch.

I forgot to add, all the web requests are processed using pl/sql procedures.

ie, the flow is
req -> apache -> oracle web listener -> pl/sql -> database -> pl/sql -> html

Most of the hits are for the search routines that i have described above.

so, does it mean that when i experimented with the free lists settings for
the search result table, the table/index is no longer in contention but
the pl/sql code?

another small doubt. I have not yet found a good utility to know if the operating system (digital unix)'s ipc, processors etc are ok. for example,
in the init.ora i have specified 2 processors. if one of them is having
problems, will it result in many locks? something to do with the spin counts?

thanks
nagarajan

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<1hpU9.22775$jM5.60756_at_newsfeeds.bigpond.com>...
> "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 Mon Jan 13 2003 - 04:22:19 CST