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

"tingl" <tlam15_at_hotmail.com> wrote in message news:f487699f.0210031124.374d139_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.
>

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.

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

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.

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

Regards
HJR Received on Thu Oct 03 2002 - 14:40:29 CDT