Re: Database Hit Ratios

"John Beresniewicz" <jberesniewicz_at_comcast.net> wrote in message news:Dg8W8.328869$_j6.15793088_at_bin3.nnrp.aus1.giganews.com...
> Richard -
>
> Nice job putting the hit ratio "controversy" into a more reasonable frame
of
> reference. Your suggestion hit ratios make sense under the assumption of
> prior minimization of logical I/O's is a good one, but therein also lies
the
> "tough nut" to crack. A well-designed system focusing on mimizing logical
> I/O for the work to be done is one that has been tuned by design and this
is
> of course the best and most cost-effective time for performance tuning.
>

Thanks for your comments.

Yes it can be a tough nut to crack, but then again if our job was too easy, anyone will be doing it (and the perception is that it is getting easier and so more unqualified people are getting involved and failing dismally). Prevention is the best cure and with memory management definitely so.

> I would venture to say that many if not most systems in deployment have
not
> had the advantage of good design-level tuning. Others with more varied
> experience in the field can refute or support this claim.
>

It depends. 'Out of the box' solutions suffer from not being "tuneable" at an early enough stage. The old debate of co-operation and early involvement between the DBA and the application designers and developers is crucial here. The earlier that a DBA (or anyone else *focused* on the *tuning* aspects of applications design), the more likely the application will hum rather than splutter along.

> Assuming this is true, then most tuning efforts will be undertaken for
> systems that exhibit an excess of logical I/O. Taking your approach, we
> should focus on finding and reducing the excess I/O rather than look at
> cache hit ratios. Here we must look at SQL and indexing and perhaps even
> logical table structure. This latter is of course the most expensive to
> monkey around with once system is deployed or nearly so. So we focus on
SQL
> and indexing for the most part.

Exactly. Reduce the logical block reads to the absolute minimum is the most crucial step. The earlier the tuning mentality is in place, the more succesful you're likely to be. The later you attempt this, the more monkey around and the less likely you'll be successful. A good hit ratio from the valid interpretation point of view requires as few logical reads as possible.

>
> One problem that arises in finding inefficient SQL is that the optimizer
> costs execution plans based on minimizing physical I/O, so plans that
> exhibit excess logical I/O may certainly be chosen. I have heard it
> suggested somewhere (Dave Ensor?) that we should locate inefficient SQL
> using a metric like "buffer gets/rows gotten" computed from V$SQL for
> instance. This won't be foolproof in all cases but seems a good place to
> start.

Agreed. Focus on those statements that access a lot of blocks for a few rows processed. The best place to start is in defining the business requirements but from a focusing on SQL point of view then yes it's a good place to start. And yes, some statements can quite legitimately access many blocks for just the one row returned. It's spotting the ugly ones that's the trick.

>
> While cache hit ratio may not be a good measure of a well-tuned system,
the
> inverse statistic cache miss ratio (Physical Reads/ Logical Reads) can be
a
> very good diagnostic indicator of problems when measured over regular time
> delta intervals. Note that this is not mere sophistry of turning the
ratio
> upside down, the trick is that we use the miss ratio as a constantly
> monitored diagnostic metric that can flag a developing problem (rather
than
> the hit ratio as a measure of overall tuning success, probably accompanied
> by a large bill for one-time consulting services).

As long as the statistics are intelligently interpreted and used in a legitimate manner from a tuning perspective then you get no arguements from me. Hit ratios can be useful in this respect is my point.

>
> Sorry for the long post. I had actually put forward an abstract for IOUG
> called "Ratios are not for Losers" but alas it was not accepted. Your
post
> kind of dredged up some of that boggle.

No longer than mine :) Sorry your abstract didnit make it :(

Maybe you should have renamed it "How To Use Ratios To Improve Your Sex Life". Getting them in is half the battle.

Regards

Richard
>
> JB
>
>
> "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 Mon Jul 08 2002 - 02:20:55 CDT