Re: Cache Hit Ratio from system views

From: Bob Jones <email_at_me.not>
Date: Thu, 16 Aug 2007 22:21:34 -0500
Message-ID: <2v8xi.1325$i75.951@newssvr19.news.prodigy.net>

<hjr.pythian_at_gmail.com> wrote in message news:1187316607.761982.153290_at_q3g2000prf.googlegroups.com...
> On Aug 17, 11:37 am, "Bob Jones" <em..._at_me.not> wrote:

>> <sybra..._at_hccnet.nl> wrote in message
>>
>> news:30p7c39h7e4nj7t1b9bnl5o0589u1upm8l_at_4ax.com...
>>
>>
>>
>> > On Thu, 16 Aug 2007 03:42:16 GMT, "Bob Jones" <em..._at_me.not> wrote:
>>
>> >>>> I clearly disagree with the statement that BHCR is meaningless. This
>> >>>> has
>> >>>> been discussed several times before. I won't waste any more energy.
>>
>> >>> Disagree all you wish Connor proved this clearly and decisively many
>> >>> years ago. And it should come as no surprise that Connor is one of 
>> >>> the
>> >>> most capable, and respected, Oracle technologists on the planet for a
>> >>> reason.
>>
>> >>I do not know who this Connor guy is. Did he really say BHCR is
>> >>meaningless?
>> >>I would love to see his proof.
>>
>> > This 'Connor guy' (a label intended as disrespectful) is Connor Mc
>> > Donald, a member of the OakTable Network. His site is
>> >http://www.oracledba.co.uk. The proof is on this site including a
>> > demonstration.
>>
>> >>I think the burden is on you to prove BHCR is meaningless. Maybe the 
>> >>issue
>> >>here is reading too many books and articles.
>>
>> > I think the burden is on you to prove it is meaningfull and to
>> > contradict the figures provided by Connor McDonald.
>> > I think the issue here is you have read to many fairy tales composed
>> > by Donald K Burleson, Mike R Ault, and other Rampant authours.
>> > May be you get paid by Burleson to bash 'savy professionals'.
>> > But most likely you are someone who like Burleson's 'savy
>> > proefessionals' just doesn't know what he is talking about.
>> > I would love to see your apologize here once you have read the article
>> > onwww.oracledba.co.uk, but I'm not holding my breath: you have
>> > established yourself here as an arrogant incompetent troll.
>>
>> Do I really need to respond to this moron?
>

> When in Rome, jump in feet first, I always say. So here goes...

> Whether Connor McDonald is respected, famous, useless, brilliant or
> whatever is irrelevant. People really shouldn't appeal to authority
> like that as if doing so is an answer in itself.

> The fact remains that in 2002, he wrote this paper:
> http://www.oracledba.co.uk/tips/choose.htm which contains a nice piece
> of PL/SQL that performs a repeated set of consistent gets until such
> time as your hit ratio goes up to any number you desire. Specify any
> hit ratio you like, you'll get it (so long as you're not already in
> excess of it).

> That piece of code conclusively proves... nothing very much except
> that if you do a lot of consistent gets, your ratio will go up. Pretty
> obvious stuff, really -though Connor's demonstration of the point is
> very neat.

>

BCHR can be manipulated. That is nothing new. All stats can be inflated in similar manners.
But that doesn't make them all meaningless. Given everything else being equal, high BCHR is always better than low BHCR.

> Smart code doesn't make a realistic argument, though. At least, not
> for some. So what, in real life, would cause lots of consistent gets
> and thus inflate the Buffer Cache Hit Ratio? Well, for one thing,
> contention for the head of the free list will mean the segment header
> block gets repeatedly 'hit' in the buffer cache. If you don't have
> enough rollback segments (or undo segments), then your undo segment
> headers will be forever being hit in the cache. If you have hot blocks
> on one or two of your tables, you'll be forever hitting those blocks
> in cache. If you rebuild your indexes every ten minutes, you'd
> probably get lots of hits in the cache. Lots of hits in the cache
> means a better hit ratio.

>

From systems point of view, performance is the amount of work done in a specific interval, regardless of the type or usefulness of the work.

> Of course, on the other hand, it may not indicate those things at all,
> but may indicate a healthy cache doing its job properly.

> Trouble is, an indicator that flashes red when things are bad and also
> flashes red when things are good isn't exactly much use, is it? Apart
> from telling you "something", you can't really work out what that
> "something" is. Which means the "something" is actually 'nothing very
> much'!

>

BCHR alone is not meant to tell performance. If it does, we would not have to look at anything else.

> I'm looking at 9 databases right now that all have 99% - 100% hit
> ratios (the databases are only 15GB in size, and each has a 5GB buffer
> cache, so that ratio is not exactly surprising). But whilst Quest's
> Spotlight is forever displaying my hit ratio in green, it is also
> flashing warnings at me that my buffer busy waits are 36% averaged in
> the last 30 seconds. I have a nasty performance problem there (caused
> by really poor physical IO distribution, actually), but the hit ratio
> is looking great.

> I think that is the point about the uselessness of the BCHR that is
> being made.

>

The issue here is again not isolating the performance factors. It has nothing to do with the meaningfulness of BCHR. It still tells you the disk I/O percentage.That is all it does.

A weight scale measures a person's weight. Of course, someone can push the person down to inflate the reading. However, we don't say the scale is useless because it doesn't always tell a person's true body weight. Received on Thu Aug 16 2007 - 22:21:34 CDT