Re: Cache Hit Ratio from system views

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.

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.

So a good buffer cache hit ratio is certainly of diagnostic value: if it's high, it may well indicate that you're suffering from performance **problems** caused by contention, insufficient undo or poor DBA maintenance practices.

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'!

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.

I know of only one mildly effective use for the BCHR. When I am told I am suffering from buffer busy waits, there are really two main potential causes: one, the cache is too small and therefore nearly every query has to hang around waiting for physical reads from disk; two, the IO subsystem is awful and blocks can't be fetched quickly enough when needed. How do you know whether its the 'small cache' or 'bad IO' cause for your particular set of nasty buffer busy waits? You could check the hit ratio. If the cause was 'small cache', one would expect the hit ratio to be poor as well as having the busy waits. If the cause was 'bad IO', you can't really predict whether the hit ratio will be good or bad, but chances are its going to be reasonable. As such the hit ratio can help in distinguishing between two equally probable causes of a problem identified from wait statistics, and even then it's all a bit woolly. Some indeed might reasonably argue that inspecting the init.ora and seeing what DB_CACHE_SIZE is set to might resolve the doubts about cache size being a factor in causing your high busy waits rather more simply, and I'm inclined to agree. Received on Thu Aug 16 2007 - 21:10:07 CDT