Re: very low hit ratio

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 Fri Apr 16 2004 - 11:38:36 CDT