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?
Comments at end
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:fG2o9.47473$g9.136529_at_newsfeeds.bigpond.com...
>
> "tingl" <tlam15_at_hotmail.com> wrote in message
> news:f487699f.0210061036.6af1f60b_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.
>
> > > 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.
>
> > 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!
>
> > > 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?
>
> >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.
>
> >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.
>
> > > 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 I
> wouldn't want to further challenge the closed mindset you evince.
>
> HJR
>
>
Reading this "discussion" I can't help thinking that in most respects you do actually agree with each other but I'm not sure you see it as you're both looking at the hit ratio from a slightly different angle.
There are two ways to tune the buffer cache, both of them important for differing reasons.
The first way is the "direct" approach, or the "bottom-up" approach, or as I like to describe it the "reactive" approach. This means looking at the "hit ratio" (or as close to an accurate interpretation as you can find) and determine whether an increase or decrease in memory is warranted based on the proportion of physical reads. What you are looking for is "the point of most return", whereby increasing in the buffer size shows no discernable improvement in the ratio but by decreasing the memory it does have a negative impact. This sweet spot is the target area and whatever hit ratio you arrive at is the appropriate hit ratio and corresponding buffer size. The hit ratio is a useful metric in determining this sweet spot which is the point Tingl is making.
The direct approach ensures the *buffer cache* is tuned (in a manner of speaking) but it potentially means little towards the tuning of the *database*. A tuned buffer cache (and a possible very high hit ratio) does not necessarily mean the database is tuned per se and is the point Howard is making.
The second way is the "indirect" approach, or the "top-down" approach or as I like to describe it the "proactive" approach. This involves looking at the underlining issues surrounding the buffer cache and appreciating the direct influences that governs it's performance. And fundamentally as blocks accessed by Oracle need to be cached in the buffer cache, reducing the number of blocks being processed is the key. And to reduce the number of blocks processed, we need to go up in the database tuning methodology and ensure the database design, the applications and the underlining SQL statements are effectively tuned. By doing so, we reduce the "logical reads" to the absolute minimum and hence take one gigantic step towards also reducing the "physical reads" to an absolute minimum and hence tune the buffer cache and the corresponding hit ratios. It's this approach which both helps to tune the *buffer cache* and the *database*.
One important point here is that reducing "logical reads" is in many ways the key to database tuning. A logical read, even if non physical, is not free. It still costs resources (and more significant than perhaps many appreciate). By significantly reducing the number of logical reads, even if the "ratio" of physical reads is unaffected will result in improved performance.
As I said at the start both ways are important, and they are when combined appropriately. By first tuning the buffer cache proactively by reducing the load on the buffer cache, you can them effectively tune reactively to ensure the buffer cache is sized as it should (via the point of most return hit ratio).
Cheers
Richard Received on Mon Oct 07 2002 - 09:01:55 CDT