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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 8 Oct 2002 06:14:51 +1000
Message-ID: <aPlo9.48130$g9.138659@newsfeeds.bigpond.com>

"tingl" <tlam15_at_hotmail.com> wrote in message news:f487699f.0210070901.12f6a24c_at_posting.google.com...
> > > > > Then I guess hit ratio is not meaningless.
> > > > >
> > > >
> > > > I said it was "largely" meaningless, and that you shouldn't "tune"
by

> >  it, as

> > > > its subject to a lot of 'fuzzy' factors.
> > > >
> > >
> > > It is either meaningful or meaningless.

> >
> > So you say. It's not what I wrote. Nor is your statement true.
> >

> > >Everything is subject to many
> > > factors including SQL tunning. We could look at this from an opposite
> > > point of view. If the performance is poor, one should not just rely on
> > > SQL tuning either. It is also subject to buffer size, but I would not
> > > say SQL tuning should be ignored or even largely meaningless because
> > > of that.

> >
> > And neither would I say that SQL tuning was largely meaningless. So don't

> > put words into my mouth, please: it's called a strawman argument.
> >
>
> I think you are getting a little sensitive here. I am simply saying
> that if you cannot say sql tunning is meaningless, the same cannot be
> said about buffer size. That's it.
>

I'm not sensitive: but I said nothing about sql tuning being meaningless, and this exchange is about buffer cache tuning. They are different things, and what one says about the one implies nothing about what can be said about the other.

> > > > It's *an* indicator. But (and here's the point you inisist on
missing)
> > it's
> > > > a pretty *poor* indicator when it needs to be interpreted so
heavily.
> > > >
> > >
> > > Like I said before, I do not consider it a poor indicator in most
> > > cases.

> >
> > Well, you are free to carry on in your beliefs and considerations. The fact

> > that you're wrong shouldn't stop you in any way.
> >

> > >I cannot recall how many times the performance was insatantly
> > > improved by simply looking at this number and adjusting the buffer
> > > size.
> > >

> >
> > Uh huh. The Niemich school of performance tuning. Seen it many times.
> >
>
> If I am seeing result, I don't think I care what school it is.
>

> > > Like I said before, a hit is a hit and a miss is a miss, regardless it
> > > is hitting the same data over and over again or hitting different data
> > > every time. The usefulness of hit ratio depends on how it is being
> > > used.
> > >

> >
> > That last sentence is what I've been saying!
> >
>
> Then how could it be "largely" meaningless and ignored.

If the ratio can be distorted, then using it as your tuning guide is going to be misleading, expensive in resources, or an incomplete job. Or all three. And it is distorted.

>
> > > > And in particular, a low ratio does not mean 'start adding more
memory',
> > > > which is what you effectively said in the post that started this
whole
> > > > exchange: "If the cache hit is too low, even pure data warehouse can

> >  benefit

> > > > from some extra memory".
> > > >
> > >
> > > How this statement could be translated into "start adding more memory
> > > when hit ratio is low" is beyond me. Even so it is not completely
> > > false.

> >
> > To coin a phrase: either it is false or it is true. Being 'not completely

> > false' is, I take it, some new state of quantum reality you've just
> > invented?
> >
>
> I did not invent it. Hit ratio is "largely meaningless" yet not
> meaningless. It is not meaningless yet can be ignored. It is not
> quantum reality, rather quantum theory.
>

"Meaning" is a subjective quality. There can be shades of meaning. Different emphases, subtle gradations. True and False are binary: you can't be both true and false.

> > >Besides hit ratio was not even the topic.
> >
> > So that makes your statement about low ratios being curable by adding memory

> > a sensible one? Nope.
> >
>
> It is a least as sensible as low ratio meaning SQL tuning.

Again, I didn't say that, but you seem to have a habit of wanting to argue with things as if I had. The ratio is misleading without heavy interpretation, therefore if I had a low ratio, I wouldn't panic. And if I had a high ratio, I wouldn't jump for joy. Therefore, if I has a low ratio, it does not mean sql tuning.

> > >I was simply saying
> > > the performance can benefit from extra memory, thus memory limitation
> > > on 32-bit matters. Of course, every statement you make here is subject
> > > to exceptions like what if the CPU is too slow or SQLs are not tuned.
> > >

> >
> > This one is subject to its own internal exceptions: the hit ratio is a poor
> > guide to whether more memory would be useful. You can throw in all sorts of
> > extraneous red herrings if you wish, but it's that central point that is at

> > issue.
> >
>
> Like I said depends on how you use it.

Uh huh. Not quite what you said at the beginning, but a concession nontheless.

>
> > > > Only when a whole lot of other indicators are taken into account
might

> >  that

> > > > statement have some grain of truth in it.
> > >
> > > That can be said about most of the indicators.

> >
> > Well, so long as you can say it about this one, we are in agreement.
> >
> > End of thread, I think, since you seem happy to do things your way, and

> > wouldn't want to further challenge the closed mindset you evince.
>
> I think we only disagree on whether hit ratio should be ignored.

No, I never said to ignore it. I said 'please don't tune by hit ratios'. Everything I've written here says that it is an unreliable indicator, and a low ratio tells you little about what performance tuning step to take next. And neither does a high ratio tell you that your database is tuned properly. When everything else has been tuned properly, then a low ratio *might* give some cause for concern.

>So
> far you really haven't produced any convincing arguments to support
> your conclusion.

That might be because you have written 'my conclusion' for me. I wasn't arguing that the ratio should be "ignored". I posted a link to a script which shows you how meaningless a particular ratio is because if you don't like it, you can select another. That was in response to several statements of yours that 'if you have a low ratio, extra cache memory can help', which implies a relationship between ratio numbers and memory settings and performance. A relationship which doesn't exit in the simplistic sense you implied it did.

>Nevertheless, it has been an intriguing discussion.

Not really. There's very little new here.

>I
> strongly encourage visitors to read the entire thread.

I would recommend visitors instead to visit Connor's site to get the script, and then visit www.oraperf.com to read the tuning white papers there. Visit www.hotsos.com, sign up for (free) membership and then read Cary's papers on why hit ratios are no basis on which to tune a database (for example, http://www.hotsos.com/dnloads/1.Millsap2001.02.26-CacheRatio.pdf).

Incidentally, that hotsos paper is a very good example of why you shouldn't "ignore" the ratio: when it's high, it's an indicator of SQL *problems*, for example. But it demonstrates quite nicely why it's not the simple 'gas guage' to performance that our other contributor to this thread thinks it is.

HJR Received on Mon Oct 07 2002 - 15:14:51 CDT