Re: Cache Hit Ratio from system views

On Aug 17, 2:28 pm, "Bob Jones" <em..._at_me.not> wrote:
> "Ana C. Dent" <anaced..._at_hotmail.com> wrote in messagenews:xT8xi.82911$kK1.41582_at_newsfe14.phx...
>
>
>
> > "Bob Jones" <em..._at_me.not> wrote in
> >news:eB8xi.1326$i75.244_at_newssvr19.news.prodigy.net:
>
> >>>> Why is BHCR meaningless? The answer should be short and simple. I
> >>>> want to hear your opinion.
>
> >>> One can not prove a negative.
> >>> Where is your proof BCHR is a reliable indicator of GOOD performance?
>
> >> BCHR alone does not tell you about overall performance. It simply tell
> >> you the disk I/O percentage. It is an indicator, a very meaningful
> >> one.
>
> > HUH? BCHR does NOT come close to measure disk I/O;
> > so by what stretch of imagination does it measure "I/O percentage"?
> > BCHR measure RAM activity says absoluting NOTHING about disk activity.
>
> Allow me to clarify, the percentage of reads from disk.
>
> > You said, "It (BCHR) is an indicator, a very meaningful one."
>
> > Please answer each below as a standlone measure of performance
> > System A has a BCHR of 22. What does it indicate?
> > System B has a BCHR of 42. What does it indicate?
> > System C has a BCHR of 62. What does it indicate?
> > System D has a BCHR of 82. What does it indicate?
>
> 22% of reads are from memory.
> 42% of reads are from memory.
> 62% of reads are from memory.
> 82% of reads are from memory.
>
> I hope you are trying to make a point here.

The point is, of what use are you going to put this information. Noone  would deny that the BCHR tells you "something". I said earlier it's a flashing red light: flashing red lights tell you something. But do they tell you your nuclear powerplant is about to reach meltdown or that it's fine and healthy? If the mere fact of flashing tells you neither or both, it's not of any *practical* value, and its undoubted information content ("I am flashing") is of no use.

It's whether that something is of any **use** that's the issue.

So if you see that 22% of your reads are from memory, is that good or bad? Do you need to increase the memory or not? If you see 100% of your reads are from memory, is that good or bad? Are you hitting block contention issues and thus inflating the BCHR, or not?

You cannot tell from the ratio itself. The ratio therefore has no prescriptive value: it doesn't tell you to increase this, reduce that, change this piece of code, move that table, rebuild that index... or indeed anything else.

The ratio is a number. The number has an ambiguous meaning in terms of actually telling anyone anything about how to tune a database. Therefore, the ratio is meaningless.

Elsewhere, you say, "Given everything else being equal, high BCHR is always better than low BHCR". I gave you examples of where a high ratio indicates a performance *problem*. Where a high ratio would be WORSE, not better, than a low ratio. You just sort of sailed over that one saying, "performance is the amount of work done in a specific interval, regardless of the type or usefulness of the work". On that basis, you could just sit there with an infinite loop calculating primes for no reason at all and chewing up all your CPU. Your database will run like crap, but your machine -on your definition- is "performing". Alternatively you could just sit there rebuilding indexes that don't actually need it because otherwise your CPU cycles will be wasted. Either way, your machine is "performing" according to you and wasting its time according to me (and, I suggest, most people's view of what constitutes 'performance').

A more rational approach is to say that "performance" is the ability of a system to carry out USEFUL work. Hammering an undo segment header block to death because the DBA hasn't sized the undo tablespace properly doesn't, on that definition count, but it will make your hit ratio higher. Meanwhile, the high ratio won't be telling the DBA 'increase the size of your undo tablespace', but an analysis of the blocks constantly subjected to buffer busy waits would.

If one were to accept that there is useful and non-useful work that a database can perform; if one were to accept that the non-useful work can inflate a hit ratio; it must therefore follow that you cannot legitimately say 'a higher ratio is always better than a low one'. And if you can't say that, then the ratio is useless. Received on Fri Aug 17 2007 - 00:35:39 CDT