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: Does anybody really use Oracle 8i on Win2k?

Re: Does anybody really use Oracle 8i on Win2k?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 5 Oct 2002 00:18:34 +1000
Message-ID: <Cfhn9.46149$g9.131997@newsfeeds.bigpond.com>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d9d6c47$0$8508$ed9e5944_at_reading.news.pipex.net...
> "tingl" <tlam15_at_hotmail.com> wrote in message
> news:f487699f.0210031124.374d139_at_posting.google.com...
> > > Please don't tune by hit ratios!!!!
> > >
> > > They are largely meaningless, as a quick trip to Connor's site
> > > (www.oracledba.co.uk) and the 'Tuning' link will show you: he has a
very
> > > nice script there, under the "Custom Hit Ratio" link, which lets you
> dial
> > > your own hit ratio. Fancy 97%.... Lo! it shall be so. 98.5% anyone??
Go
> for
> > > it: the script makes it so.
> >
> > I have to respectfully disagree. Even something similar to the script
> > were part of your normal system activity, the hit ratio is still
> > meaningful. But it is about as uncommon as gauging mpg with a fast
> > leaking gas tank.

>

> It's meaning is that *for the sql that you are running* such and such a
> proportion of the execution plan that was chosen was answered from memory
> not disk. It doesn't however tell you anything about how good the SQL is
or
> how good the plan is. Try this thought experiment (or if you are brave try
> it in your office).

>
> Divide a group of people into 2. One group of 2 people, one group of
twenty.
> Get the group of twenty to stand up. Each group has to pass a message
> chinese whisper style to the next person say 'David Bowie is mentioned too
> often around here'. The other rule is that you have to be standing up
before
> you can pass the message on (so the group of 2 have to stand up first).
> Which group will win in a race? The answer is the group that passes on
fewer
> messages (or does less work) this is despite the performance penalty of
> having to standup first.

>
> So it is with SQL the fewer reads (of whatever sort) you need the less
work
> you do and the faster the system goes. However hit ratios tell you nothing
> whatsoever about how many reads you are doing.
>

Hi Niall,

I protest !!

What kind of sickos are you using for this experiment ;)

This is my spin on buffer hit ratios (and I guess hit ratios in general). The buffer hit ratio gives you a indication on the specific performance of the *buffer cache*. If somewhat low and by adding more memory you improve the hit ratio and hence reduce the occurrences of physical reads, then you have "tuned" (I use the word advisedly) the buffer cache. I view this as putting oil into the engine.

However, does having a high buffer hit ratio mean the *database* is tuned. Does having a high hit ratio for a specific *SQL Statement* mean the SQL statement is well tuned. The answer to both is an emphatic "no way man" !! Does having sufficient oil in a car engine mean it has that beautiful purring noise as you hit 160Ks/hour.

Of course not.

Because if you have a driver (insert user/application) who doesn't know how to change gears and wonders why the car *in first gear* is making a racket when it's only hitting 50ks tops, then all the oil in the world is not going to make any difference to the performance of the car. In fact the car is doing a tremendous amount of work but in an incredibly inefficient way. If only we bumped up a few gears, we would actually reduce the work the engine is doing and improve performance as well. Putting more and more oil in the engine could actually be making matters worse not better as the real issue (crap driver) is not being addressed.

As DBAs, we are of course responsible for the tuning and the maintenance of the "Oracle Engine" but we *must* also be driving instructors and ensure we have no bad drivers around. And ultimately, the more "Michael Schumacher" applications we have, the greater the likelihood our databases will fly with F1 efficiency.

Do you like it :)

Cheers

Richard Received on Fri Oct 04 2002 - 09:18:34 CDT

Original text of this message

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