I thought Mr. Bowie couldn't be mentioned enough.
Jim
"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.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> >
> > >
> > > Any ratio you fancy, and without a single bit of extra memory being
> > > allocated to the Buffer Cache? The only proper conclusion is that the
> hit
> > > ratio is a profoundly meaningless tuning goal. Used cautiously, it
*may*
> be
> > > a reasonable clue as to performance problems, but its not an end in
> itself,
> > > and a low ratio definitely doesn't mean 'bung in some extra memory'.
> > >
> >
> > Nevertheless cache hit ratio is still the primary indicator of buffer
> > usage. Otherwise, we would just ignore the buffer size and keep
> > running the script for performance improvement.
>
>
Received on Fri Oct 04 2002 - 09:51:02 CDT
