Re: very low hit ratio

OK, OK - Niall & Howard. I made a lousy statement which is objectively incorrect. In my environment I usually get a 98-99% hit ratio and that does not mean, as both of you have pointed out, that it is a good target for every system. I agree that there is no single measure that provides a magic bullet. A high hit ratio value can be obtained by repeated table scans in
memory by a poorly constructed query or queries. (I usually detect this by a big jump in the number of hits.)

In the post that I was responding to:

"I get a data buffer hit ratio of more than 90%. The users still yell at me that the database is not responding ! So it really depends. If your users do not yell at you, forget about the hit-ratio and have a good sleep."

My take was that he was inferring "I get a high hit ratio and therefore the buffer cache is adequate." I disagree with that position (if that was what he
was inferring). Unfortunately, my posted response was very, very different from my intent.

p.s. I was wondering if your comments were based upon the online chapter or the whole book?

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:40800c10$0$25657$afc38c87_at_news.optusnet.com.au...
> Carl Kayser wrote:
> > I don't know about DW (and I'm primarily a Sybase DBA) but 90% hit ratio
> > means a 10% miss ratio which I do not consider to be good.
>
> Why?
>
> What's more important? That you get a hit and make your ratio look good;
> or that your queries are efficient and don't need to access that block
> at all?
>
> A ratio without a workload is nonsense; a meaningless average. And it's
> far more important to tune the workload than it is to worry about
> particular numbers on a ratio.
>
> > Consider chapter
> > 7 of Alan Packers book which is available (along with the TOC) online at
> >
> >

http://www.amazon.com/gp/reader/0130834173/ref=sib_dp_rdr/104-8369762-3779959#reader-page
>
> I read it, and I note that even he says he hasn't a clue what the
> "right" number for the buffer cache hit ratio should be, since three
> different tuning books mentioned three different values. He then pulls
> off quite a clever stroke, which is to concentrate on halving or
> quartering the miss ratio... but however neat that is, it's just a
> writer's trick that has you concentrating on improving the hit ratio at
> the end of the day.
>
> He also hints, but doesn't dwell on, the fact that the degree of index
> access versus full table scans affects the ratio. The skewness of your
> data affects the ratio. And, at one point, he comes right out and says it:
>
> "The objective of monitoring the buffer cache hit ratio is not to
> achieve some arbitrary number; it is to optiize performance by finding a
> balance between memory consumption and disk I/O"
>
> ...which is one of the more intelligent statements on the subject I've
> seen. Based on that statement, how can you -or anyone- say "10% miss
> ratio... I do not consider that to be good"??? You've just elevated some
> "arbitrary number" to exalted target status, and just lost site of the
> 'balance between memory and disk I/O' goal. Which is the perennial
> danger of simplistic ratios.
>
> What your author doesn't go on to say, perhaps because his book is a
> generalist one, aimed at the Sybase and DB2 markets as well as the
> Oracle one, is that there are *better* ways of monitoring this balance
> between memory consumption and disk I/O in Oracle than a simplistic
> ratio. If the balance is skewed badly and ill-advisedly towards disk
> access, you will know all about it in Oracle because you'll suffer from
> measurable free buffer waits, for example. So if you tune free buffer
> waits out of your database, you can quite comfortably state that disk
> I/O is not a problem without ever one having to calculate a buffer cache
> hit ratio.
>
> Oracle's wait events interface is a rich source of detailed and specific
> tuning information that cannot be fudged in the way a ratio can. And
> that's why it's far more sensible to concentrate on eliminating waits in
> Oracle than it is to spend time worrying about a largely meaningless
ratio.
>
> Regards
> HJR
>
>
>
>
Received on Sat Apr 17 2004 - 13:31:49 CDT