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

"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.