Re: Cache Hit Ratio from system views

From: Bob Jones <email_at_me.not>
Date: Tue, 21 Aug 2007 04:24:48 GMT
Message-ID: <kOtyi.50198$YL5.8637@newssvr29.news.prodigy.net>

<hjr.pythian_at_gmail.com> wrote in message news:1187328939.022236.195040_at_i13g2000prf.googlegroups.com...
> 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.
>

I don't know of a performance stat which is capable of that.

> 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?

Without any other information? I will need my crystal ball.

> 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?

>

I will say it again. BCHR is not the only thing to look at. Overall performance depends on many factors. I can also ask similar questions. Is my performance good or bad if I have low block contention? Does it tell me if my buffer cache is too small?

> 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.

>

That may be expecting too much from a single performance stat.

> 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.

With everything else being equal?

>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').
>

There is no performance stat I know can tell whether the system is doing useful work from human's perspective. Just like a speedometer, it can tell you the speed, but it cannot tell if you are circling the same block.

> 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.
>

What if the DBA has sized the undo tablespace correctly? Does buffer busy waits tell you anything about performance?

> 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.
>

A machine cannot possibly know whether it is doing "useful" work. Non-useful work inflates many things not just hit ratio. High BCHR is always better than low - provided everything else being equal. If BCHR is useless for the stated reasons, no other indicator would be useful. Received on Mon Aug 20 2007 - 23:24:48 CDT