Re: Database Hit Ratios

"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 - 01:55:20 CDT