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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sun, 6 Oct 2002 05:51:54 +1000
Message-ID: <KhHn9.46823$g9.133507@newsfeeds.bigpond.com>

"tingl" <tlam15_at_hotmail.com> wrote in message news:f487699f.0210051022.15c44b25_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.
> > > > If your SQL accesses 20 vital records with 3000 buffer gets, 2998 of

> > which

> > > > are 'hits' in the cache, you have a hit ratio near 100%.
> > > >
> > >
> > > This at least tells you that increasing the buffer size will not help.
> > >
> >
> > That's not a bad deduction, actually.
> >
>
> 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.

[snip]
> >
> > Actually, by all the standard measures, 80% is pretty bad. It's supposed to
> > be 85% or thereabouts for a warehouse, and 95% for an OLTP shop, according
> > to Oracle's opwn doco, and a raft of rather naff books out there under > > various labels. And whilst your earlier deduction (an increase isn't going
> > to help) was sound, the one here (that an increase might be useful) is much

> > more problematic.
> >
>
> You loss me a little bit here. You mentioned hit ratio is useless, now
> I hear 80% is pretty bad. In general sense I don't think it's bad for
> data warehouses.
>

I wish you'd learn to read. I said "by all the standard measures" that 80% is bad, meaning that the usual Oracle myth-makers promote that idea. And yes, it's supposed to be bad for a data warehouse, too. If you must insist on treating the buffer cache hit ratio as though it had some special significance, at least take the trouble to find out what sort of ratio you should actually be aiming for. Oracle Press books, Richard Niemic, and Oracle's own documentation (to name but three) say that 85%+ is OK for data warehouses, and it should be 95% or more (actually, the phrase they use in the Oracle training material is "in the high 90s") for OLTP.

The fact they say it, however, doesn't make it true.

> > The general point is good though: with statements such as these, you aren't

> > tuning by hit ratios. You are using the ratio as a starting point for
> > thinking about what is going on in the cache specifically, the database
> > generally, and the application globally. And *that's* the basis of good
> > performance tuning.... not simply saying "80% is bad, 95% is good".
> >
>
> Hit ratio is simply one of the indicators.
>

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.

[snip]

> It is one aspect of tuning and should not be overlooked. Here is
> another analogy: If I am driving down the road with a fast leaking gas
> tank, should I be ignoring the gas meter because it does not reflect
> the actual amount of gas my car is burning? No.

It's a poor analogy. Because the gas meter is an accurate measure of how much gas you've got in your gas tank. The buffer cache hit ratio isn't even a particularly accurate measure of how much data you are 'hitting' in the buffer cache. It's also poor because there is a direct correlation between the continued presence of gas in the tank and the ability of the car to keep moving (no gas=no power=stationary), so of course it makes sense to keep an eye on the guage. There is no such correlation between a hit ratio and database performance. A 60% ratio is neither good nor bad, and doesn't tell you anything very much, on its own, about whether your database is optimally tuned. Likewise for 100%. So if the 'guage' is as feeble a guide as that, then it's perfectly legitimate to not be terribly concerned about it.

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

Only when a whole lot of other indicators are taken into account might that statement have some grain of truth in it.

HJR Received on Sat Oct 05 2002 - 14:51:54 CDT