Re: Cache Hit Ratio from system views

On Aug 18, 12:22 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Aug 17, 1:35 am, hjr.pyth..._at_gmail.com wrote:
>
>
>
> > 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. No-
> > one 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.
>
> Lots and lots of words in this thread by multiple people on a subject
> that is dead and buried many years ago.
>
> Must be a slow day somewhere.

Actually, the subject's not dead and buried at all. I wish it were, but only 6 weeks ago, I recently prepared an 8 page, detailed explanation of why the BCHR was not a reliable guide to anything and the boss to whom it was presented came back to me within 4 minutes and said, "Well, it's interesting from a theoretical point of view, but practically, a high hit ratio has got to be better than a low one, no?'

It's a very common fallacy, even today. And even amongst those who do DBAing for real and have done so for some time. Why, even the world's Greatest Oracle Expert still bangs on about this stuff.

I therefore have no problem spending a bit of time trying to kill this particular dragon one more time, and I don't consider it a waste of time doing so. Received on Fri Aug 17 2007 - 19:29:10 CDT