Re: the ways of buffer hit ratios are mysterious

We have similar problems managing a large SAP R/3 system on oracle.

What you need to do is look at the statistics relating to the worst offending sql that is to be regularly found in the Library Cache and analyse that with a view to seeing if any extra indexing is required. Only add one when sure this will improve things rather than worsen them.

As you knock off the worst offenders the general performance of the Buffer Cache may well improve.

However Mark made a good point; we are not here to improve the statistics - only to improve the performance. The stats are an 'attempt' to quantify the performance.

regards

--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United Kingdom)

                        <Kevin_A_Lewis_at_Hotmail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. attwoody <attwoody_at_my-deja.com> wrote in message news:83dr65$nn$1_at_nnrp1.deja.com...
> Greetings, all
>
> I have some twelve (12) Oracle books, most of them dealing with tuning.
>
> In all of them, there are discussions about the Buffer Cache Hit Ratio,
> with the discussion being, "Thou shalt increase thy DB_BLOCK_BUFFERS in
> order to increase thy hit ratio"
>
> I've reorganized the database, increased DB_BLOCK_BUFFERS,
> rebuilt indexes, added indexes, etc, etc, etc. I've gone into the
> x$tables, I've set DB_BLOCK_LRU_EXTENDED_STATISTICS to see the
> effects of subtracting or adding buffers.
>
> It seems no matter how much I tweak and tune - some days, the hit
> ratio is in the 90's, some days it's in the 70's.
>
> Since the application is PeopleSoft 6, there's not a lot I can do
> to the code.
>
> It occurred to me that PeopleSoft has a lot of little tables that
> are used to retrieve data, and that a lot of table scans are being
> done - could this be contributing to what I'm seeing with the hit
> ratio? Would it be better to cache these small, frequently used
> tables?
>
> Another application I support called Glovia, has _excellent_
> performance statistics - but Glovia is _specifically_ written for
> Oracle - it has lots of stored procedures and packages, and takes
> advantage of Oracle performance enhancements. PeopleSoft's standard
> answer is "we want to make everything generic so it can run on
> any database". Well, it makes _my_ life more difficult!
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Dec 20 1999 - 04:21:50 CST