Re: Database Hit Ratios

Hi Howard,

There's a fly buzzing around near you :)

Richard
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:agbcvm$v8p$1_at_lust.ihug.co.nz...
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:TcaW8.30271$Hj3.92161_at_newsfeeds.bigpond.com...
> > Hi Howard,
> >
> > I must admit, of all the various Oracle courses I taught, I regarded the
> > Performance Tuning course as the weak link. It really doesn't tackle the
> > issue of performance tuning at all well, is poorly structured, has a
poor
> > workshop and misses the point too many times. You simply don't tune a
> > database the way the course suggests. It requires an instructor to "add"
> > valid interpretations on what being discussed to be successful. I
remember
> > walking in on a performance tuning course on day 3 because of illness,
> being
> > stunned and horrified on what was said and taught in the first 2 days
(no
> > names). I basically had to re-teach everything in the 3 remaining days.
> >
> > I dislike generalisations (you may have noticed) and I guess the point
I'm
> > trying to make is that suggestions such as hit ratios are *always*
useless
> > and the such strikes me the wrong way. My natural instinct is to say,
but
> > what about ...
> >
>
>
> Agree, agree. I just don't think you'll have seen anyone saying hit ratios
> are "always" useless.
>
> They *are* always useless as a tuning goal.
>
> But not as a diagnostic in their own right.
>
> If I've ever said anything otherwise, I expect a swarm of past students to
> post in right now.
>
> Bzzzzzzzzzzzz
> HJR
>
>
>
> > Regards
> >
> > Richard
> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:agb4kv$o0q$1_at_lust.ihug.co.nz...
> > > 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 Mon Jul 08 2002 - 02:16:20 CDT