Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Hit Ratios

Re: Database Hit Ratios

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 8 Jul 2002 16:29:40 +1000
Message-ID: <TcaW8.30271$Hj3.92161@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 ...

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:29:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US