Re: Database Hit Ratios

Richard Foote wrote:
>
> Hi All,
>
> I know I'm sticking my neck out here, but recently many people have been
> commenting that database hit ratios matter about as much to database tuning
> as Mike Tyson matters to world peace (ie. not a lot). I've read the
> excellent articles by Gary Millsap, seen the very clever dial up script by
> Connor McDonald and read many comments here. I'm sure I'm being pedantic,
> but (gulp) I disagree with any final conclusion that suggests hit ratios
> *never* matter. What's important (and this is what I've been failing to
> hear), is that hit ratios matter IF *interpreted* and used in a logical and
> meaningful manner.
>
> Let me position my case, with an emphases on the database buffer cache hit
> ratio. Firstly people who tune " SQL statements" based on hit ratios are
> wrong. People who tune databases to a "specific" hit ratio are wrong. People
> who think a database hit ratio of 99.99 means their database is king s**t
> are wrong.
>
> In fact before you seriously look at memory performance, you *must* follow
> the correct (and in my opinion only) tuning methodology. That is first get
> your business requirements accurate and appropriate. Then get you database
> modelling and data design accurate and appropriate. Then implement your
> database design efficiently and appropriately. Then get your applications
> designed efficiently, accurately and appropriately. (Difficult for out of
> the box solutions I know...). The net effect of all
> this is that you now have a database that has been tuned to do the *minimum*
> possible amount of work to satisfy it's existence. This means you have
> reduced the number of *logical* I/Os down to an absolute minimum (which at
> the end of the day is what 95% of database tuning is all about). Unless this
> has all been effectively performed, then memory tuning is a little like a
> mechanic tuning a car only to have the driver plod away in first gear !!
>
> Now we get to the hit ratio and it's useful and valid interpretation from a
> tuning perspective. I've seen Conner's "dial up" hit ratio example (and
> clever it is too) but I view it as an invalid way to discredit the *valid
> interpretation* of the hit ratio. Invalid in that it runs a little script
> that
> accesses (as many times as necessary) a cached table that increases the
> logical reads to the point the physical reads are an inconsequential
> percentage of the total logical reads. High hit rate, c**p DB, hit rates
> mean nothing is the conclusion. But as already stated, a good DBA would have
> detected this rather naughty script, it's irrelevance to business
> requirements and out she goes. Reducing the logical I/Os to the bare minimum
> is the prerequisite to memory tuning and the *valid interpretation* of the
> hit rate.
>
> In fact, it's the *physical* I/Os that are of most importance here in
> relation to the hit ratio. Once and only once the logical I/Os have been
> tuned, do we need to determine the *number* of physical reads. If by
> increasing my memory buffers, I'm reducing the physical I/Os to the point
> where it's a worthwhile return in investment, then I'm altering the *hit
> ratio* in a valid and deterministic manner. Once the physical reads have
> plateaued out, then my tuning is done. The *value* of the hit ratio is of no
> consequence, but the behaviour pattern of reducing the physical reads (and
> thus it's effect on the hit ratio) is important. And Oracle supports this
> (bless them), why else do they go to such trouble as providing us with all
> the db cache advise stuff, so we can effectively tune the buffer cache to
> reduce physical I/Os and yes, (in)directly the hit ratio performance as
> well.
>
> The buffer cache can actually be too big and hurt performance so reducing
> it's size without any detriment effect on the physical I/O count is also a
> valid tuning outcome. Again, using my interpretation of the hit ratio
> statistics to positive effect.
>
> I now have a database with the minimum number of *logical* I/Os and the
> minimum number of *physical* I/Os. MY INTERPRETATION OF TUNING BASED ON HIT
> RATIOS and I go back to what I said at the start. It's not just the
> statistics that constitute the hit ratio that are very important, but how
> they are interpreted. And when people say that hit ratios matter not, it's a
> blanket statement that worries me because it's somewhat in the eye of the
> beholder exactly what that statement means. It's a correct statement from
> many interpretations but not *all*. Yes, a 95.28% hit ratio is meaningless
> but the number of logical and physical I/Os is very very important.
>
> To those that I'm stating the bleedin obvious please forgive me, but it's a
> subject that has caused much confusion and this is my little attempt to
> clarify (confuse?) the matter.
>
> Ok, my eyes are close. Hit me.
>
> Richard

Oooh - my name's been mentioned - time to jump in :-)

I've recently given a presentation at some user group meetings here in the UK, which I think better explains my position on cache hit ratios.

My main problem is the "sequencing" (for lack of a better term) which I'll try explain thus (with apologies in advance for length):

When I first got into the IT industry all I did was cobol/vsam and on the odd lucky occasion, some DB2. But when we wrote a program that didn't run as well as it should have, the sysops would simply chuck it out of the job class and abend the thing. There was only one solution - you changed the code to make it more efficient. There was no "can we get more cpu, can be get more ram" - hell, we didn't even know how much resources the mainframe had - it just ran things. The emphasis was simple - if your programs run poorly - you need to fix the program.

Nowadays, (imho) we've forgotten this history - and I'm not just referring to cache hit ratios. Problem: The program sorts too much, Soln: increase the sort area size. Problem: Too much IO, Soln: bump up the buffer cache. Even on a home PC, OS's have that remarkable ability to consume all possible resources. It seems that the *last* thing on the list of things to do is fix the program, ie, the "sequencing" is all wrong.

Perpetuating that problem was that not only did we start throwing resources at problems, we started using how much we threw as a measure of improvement! (cache hit ratio being the obvious candidate).

I think maximising of the *effectiveness* of the cache (buffer, library, whatever) is vital to a DBA's skill repetoire - but that's vastly different to trying to get your database to have hit ratio of 'x' percent.

Cheers
Connor

PS - in terms of DBA finding the rampant "choose_any_hitratio" code, one could always wrap it up in DDL to make identification all that much harder :-)

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."