Re: Cache Hit Ratio from system views

On Aug 21, 2:24 pm, "Bob Jones" <em..._at_me.not> wrote:
> <hjr.pyth..._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.

Well, you obviously have a closed mind on the subject, so it's now pointless debating you in particular any further on the matter.

For anyone coming later, who has more of an inquiring (and logical) view of things:

1. If the BCHR is useless, that says nothing at all about the usefulness of other indicators. Asserting otherwise is equivalent to saying, 'because I am allergic to penicillin, I must also be allergic to aspirin". It simply doesn't follow that if one statistic is useless, others must be too.
2. The point about 'useful' work was that BCHR cannot distinguish between lots of hits because all your juicy data is being cached nicely and lots of hits because you've got massive undo segment header contention (for example). When, however, you measure buffer busy waits on undo segment header blocks; or if you measure excessive waits for the US enqueue, you've got very good evidence that your database is encountering a poor undo configuration issue and a pretty good clue as to how to go about fixing it. It's the specificity of the measure that's at issue. Good statistics have focus. They don't conflate many different ('useful' and 'useless') factors into one meaningless number.
3. There are lots of performance stats in Oracle that are highly focussed and very prescriptive. A high reloads/pins ratio in your library cache tells you your library cache is too small, for example; a poor parse/execute ratio tells you you should use more bind variables in your code. And so on. The Oracle database is extremely well-instrumented with highly-detailed statistics that permit one to focus very closely on problem areas. The BCHR just happens not to be one of them