| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Does anybody really use Oracle 8i on Win2k?
> > > > > > > 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
> > > > > > > nice script there, under the "Custom Hit Ratio" link, which lets
> > > > > > > your own hit ratio. Fancy 97%.... Lo! it shall be so. 98.5%
> > > Go > > > for > > > > > > > it: the script makes it so. > > > > > > > > > > > > I have to respectfully disagree. Even something similar to the
> > > > > > 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
> > > > > 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. >
It is either meaningful or meaningless. 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.
> > > > > > Actually, by all the standard measures, 80% is pretty bad. It's supposed
> > > be 85% or thereabouts for a warehouse, and 95% for an OLTP shop,
> > > 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
> > > to help) was sound, the one here (that an increase might be useful) is
> > > 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. >
I agree with your last line here. I have also read books about something way below 80% hit ratio for data warehouses. In reality I rarely see data warehouses have over 80% hit ratio. That's really besides the point. I was just surprised that you brought it up even the hit ratio is so insignificant to you.
> > > The general point is good though: with statements such as these, you
> > > 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. >
Like I said before, I do not consider it a poor indicator in most cases. I cannot recall how many times the performance was insatantly improved by simply looking at this number and adjusting the buffer size.
> > 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. >
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.
> 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. Besides hit ratio was not even the topic. 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.
> 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. Received on Sun Oct 06 2002 - 13:36:40 CDT
 |
 |