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

From: tingl <tlam15_at_hotmail.com>
Date: 5 Oct 2002 11:22:55 -0700
Message-ID: <f487699f.0210051022.15c44b25@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.
> > > >
> > >
> > > PhhhhhhhhhhhhhtBong!  (That's the sound of something completely missing

> the

> > > point).
> > >
> > > How something can be "meaningful" when it can so easily be manipulated

> is

> > > beyond me. All Connor is doing with his script is generating large

> amounts

> > > of consistent reads. Those happen all the time in the normal operation

> of a

> > > production database. They inflate the hit ratio. Rollback segment

> headers

> > > can suffer enormous degrees of contention, and thus be a real

> performance

> > > bottleneck -yet each time they're accessed, they register as a 'hit' in

> the

> > > cache, inflating the ratio. Segment header blocks are consulted (and

> maybe

> > > adjusted) by all inserts and deletes, which mean they are going to be
> > > popular blocks, and thus constantly 'hit' in the cache. You set PCTFREE

> for

> > > a segment to 95 and PCTUSED to 90, and you'll have to make freelist
> > > alterations practically every piece of DML: performance will be woeful

> as a

> > > result, but you'll have a nice hit ratio because all the times you

> needed to

> > > adjust the segment header block, there it was in the Cache.  I could go

> on.

> >
> > PCTFREE 95, PCTUSED 90? I don't know.
> > I completely understand your point. Certain activities will no doubt
> > inflate the hit ratio (althought not significantly on most real
> > systems) but they are in fact happening on your system. A hit is a hit
> > and a miss is a miss. Are you suggesting thoes numbers should not be
> > counted? The hit ratio simply tells the hits and misses on the system,
> > nothing else. It is up to you to interpretate it but it is by no mean
> > meaningless. Otherwise we would not have to use it at all.
> >
> > >
> > > 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.  

> > > My SQL accesses those same 20 vital records with 50 gets, 40 of which

> are

> > > 'hits' in the cache. My ratio is only 80% ("Bad" by all the usual

> measures).

> > > But I'll guarantee you my 20 records are returned faster than yours.
> > >
> >
> > This tells you that you may be able to improve by increasing the
> > buffer size, although 80% is not bad. What if the hit ratio were 30%?
> >
> 
> 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.

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

> >
> > After all the SQL tunings you will still have to come back to buffer
> > size eventually, or we will all be using minimal amount of memory
> > possible and expect optimal performance.
> 
> In a nutshell, that is what performance tuning is all about, surely: optimal
> performance with minimal resources.
> 
> >There are also things that
> > will deflate the hit ratio but they are indeed occurring and should
> > not be ignored.
> 
> Exactly.  The ratio is being messed around with every which way. So don't
> use it as the be-all and end-all of tuning. That's all.
> 

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. Received on Sat Oct 05 2002 - 13:22:55 CDT