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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 5 Oct 2002 06:42:10 +1000
Message-ID: <SWmn9.46236$g9.132076@newsfeeds.bigpond.com>

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

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

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

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

[snip]
> >

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

Regards
HJR Received on Fri Oct 04 2002 - 15:42:10 CDT