Re: Poor Buffer Hit Ratio

I just meant that some idea of the nature of the Application(s) should be attained.

Then as you say, the performance metrics can be judged in their correct light.

Until the nature of the Application(s) is known, then my point was that it was not worth looking at statistics such as the buffer cache hit ratio in isolation.

I knpw the original post mentioned buffer cache hit ration but I think this is 'putting the cart before the horse' i.e. maybe a more global view should be reached first, hence my previous comments about studying the kind of SQL that's being handled by each instance.

Of course, if the SGA's are being paged out to disk, then a serious performance improvement should occur as soon as the SGA's are reduced in size.

Depending on how OLTP Versus DSS the Applications are individually, this should ultimately reflect in the buffers allocated to each - less buffers for DSS (poor buffer re-use in DSS) and more for OLTP.

David P.

Oracle Certified DBA.

> >
> Why do you say that? The original post just refered to the buffer hit
> ratio and my post of a note from Oracle states that for this version of
> Oracle the buffer pool statistics are not even maintained correctly by
> Oracle so you can not put much weight on the value you calculate for
> the buffer hit ratio. And for version 8.1+ the formula has to be
> adjusted for another io statistic. The buffer hit ratio should be used
> in conjuction with the other buffer pool statistics before any
> conclusions are reached.
>
> More importantly, a hit ratio of 70 can be acceptable. For an OLTP 70
> is normally too low, but not always, while for DSS, OLAP, or warehouse
> it may be fine. In fact it can be fine for an OLTP when the OLTP is
> very large and the IO is very random. What counts is the application
> performance. The ratio is best used for trend analysis, i.e., it is
> level, trending up, or trending down. It is not very meaningful in
> itself and is often given too much weight since one bad SQL could be
> causing the low hit ratio or it could just be how the application works.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu May 11 2000 - 00:00:00 CDT