Re: Database Hit Ratios

Start again,

Hi Connor,

Sorry

Rihcadr
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:UltW8.31033$Hj3.93863_at_newsfeeds.bigpond.com...
> Hi Conner,
>
> I thought some free advertising can't hurt :)
>
> I completely agree.
>
> The 'chuck more resources to fix it', the 'she'll be right mate we'll
worry
> about all that tuning stuff later' mentality is very prevalent at the
> moment. Resources are (relatively) cheap and DBAs are (relatively)
> expensive. Problem is of course that resources is not *A* solution, never
> mind *THE* solution. I've never seen a 100 million row FTS out perform a
> single row index access not matter the available resources. A skilful
Oracle
> DBA is still a very valuable (and cost effective) commodity.
>
> I just wanted to stimulate some thinking on all this. Thanks heaps for
being
> a part in my being able to do so.
>
> Regards
>
> Richard
>
> PS. I knew Hewitt could do it. Go you aussies !!
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:3D29E90F.7DF2_at_yahoo.com...
> > Richard Foote wrote:
> > >
> > > 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
> >
> > Oooh - my name's been mentioned - time to jump in :-)
> >
> > I've recently given a presentation at some user group meetings here in
> > the UK, which I think better explains my position on cache hit ratios.
> >
> > My main problem is the "sequencing" (for lack of a better term) which
> > I'll try explain thus (with apologies in advance for length):
> >
> > When I first got into the IT industry all I did was cobol/vsam and on
> > the odd lucky occasion, some DB2. But when we wrote a program that
> > didn't run as well as it should have, the sysops would simply chuck it
> > out of the job class and abend the thing. There was only one solution -
> > you changed the code to make it more efficient. There was no "can we
> > get more cpu, can be get more ram" - hell, we didn't even know how much
> > resources the mainframe had - it just ran things. The emphasis was
> > simple - if your programs run poorly - you need to fix the program.
> >
> > Nowadays, (imho) we've forgotten this history - and I'm not just
> > referring to cache hit ratios. Problem: The program sorts too much,
> > Soln: increase the sort area size. Problem: Too much IO, Soln: bump up
> > the buffer cache. Even on a home PC, OS's have that remarkable ability
> > to consume all possible resources. It seems that the *last* thing on
> > the list of things to do is fix the program, ie, the "sequencing" is all
> > wrong.
> >
> > Perpetuating that problem was that not only did we start throwing
> > resources at problems, we started using how much we threw as a measure
> > of improvement! (cache hit ratio being the obvious candidate).
> >
> > I think maximising of the *effectiveness* of the cache (buffer, library,
> > whatever) is vital to a DBA's skill repetoire - but that's vastly
> > different to trying to get your database to have hit ratio of 'x'
> > percent.
> >
> > Cheers
> > Connor
> >
> > PS - in terms of DBA finding the rampant "choose_any_hitratio" code, one
> > could always wrap it up in DDL to make identification all that much
> > harder :-)
> >
> > --
> > ==============================
> > Connor McDonald
> >
> > http://www.oracledba.co.uk
> >
> > "Some days you're the pigeon, some days you're the statue..."
>
>
Received on Mon Jul 08 2002 - 23:49:36 CDT