Re: Database Hit Ratios

I'll give another example. See if that makes it clearer.

The 9i Performance Tuning course, in Chapter 14 (read that and weep : FOURTEEN!!) has as its first page a slide which reads "Application Design is the most important aspect of performance tuning" -to which my first response is "why the hell is it in chapter 14 then????", because it is undoubtedly true.

Of late, I have given up trying to explain why the most important aspect of performance tuning should only be discussed on Thursday, and instead have decided to teach Chapters 1 and 2 (introductory material) followed by Chapters 14, 13, 11 and 12. ("Exotic" segment types, SQL tuning, MTS and statistics collection). By the fourth day of the course, we are ready to deal with "Memory" issues (Chapters 3, 4 and 5).

Guess what? By Thursday, our test databases have perfectly good hit ratios. We've converted half the tables into Index Clusters and IOTs. Our SQL is issuing with a plethora of bind variables. We've got histograms on all the right columns. Our execution plans are sound. And as a result, there's sod-all else to tune.

The tuning there is still to do largely consists of making sure that the large tablescans don't flush the cache by sensible assignment of segments to appropriate buffer pools.

Only then do I mention the hit ratio. And Lo! It is fine. What remains to be tuned is woeful Redo Log performance, and that's only because the initial configuration is so bloody-mindedly awful.

Point is, all the hit ratios on Thursday tell us is how much we've learnt on the previous 3 days. A nice confirmation. A poor diagnostic.

Regards
HJR "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:agauem$idp$1_at_lust.ihug.co.nz...
> I think it obvious that hit ratios as a tuning *goal* are pathetic, and
you
> say as much with the 'interpretation is king' comments below.
>
> But that's precisely what certain tomes out there have promoted, Oracle's
8i
> course material included. The classic is Richard Niemic's statement along
> the lines of 'increase your hit ratio from 97 to 98% and your database
will
> perform 4 times faster'. Utter bollocks, of course, because it ignores the
> most important element, which is *context*. What sort of a database is
this?
> What SQL is it issuing? What is the SQL waiting for?
>
> As a *symptom* of tuning problems, fair enough. And symptoms need
> diagnosis -sometimes, they are coincidental and not germane to the
> diagnosis. Sometimes they are.
>
> No-one is suggesting never to calculate the ratios. But pursuit of a ratio
> for its own sake is pointless, and a 99% buffer cache hit ratio doesn't
mean
> you haven't got problems.
>
> Regards
> HJR
>
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:C46W8.30154$Hj3.91655_at_newsfeeds.bigpond.com...
> > 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
> >
> >
> >
>
>
Received on Sun Jul 07 2002 - 23:33:05 CDT