Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Does anybody really use Oracle 8i on Win2k?

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

From: tingl <tlam15_at_hotmail.com>
Date: 4 Oct 2002 09:30:42 -0700
Message-ID: <f487699f.0210040830.139a0108@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.

> 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%?

> > >
> > > Any ratio you fancy, and without a single bit of extra memory being
> > > allocated to the Buffer Cache? The only proper conclusion is that the
> hit
> > > ratio is a profoundly meaningless tuning goal. Used cautiously, it *may*
> be
> > > a reasonable clue as to performance problems, but its not an end in
> itself,
> > > and a low ratio definitely doesn't mean 'bung in some extra memory'.
> > >
> >
> > Nevertheless cache hit ratio is still the primary indicator of buffer
> > usage. Otherwise, we would just ignore the buffer size and keep
> > running the script for performance improvement.
>
> Summed it up in a nutshell. Go ahead, ignore the buffer size and concentrate
> on tuning your SQL so it accesses fewer blocks. That'll probably pay more
> dividends.
>

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. There are also things that will deflate the hit ratio but they are indeed occurring and should not be ignored. Received on Fri Oct 04 2002 - 11:30:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US