Re: Cache Hit Ratio from system views

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. Received on Fri Aug 17 2007 - 09:22:00 CDT