Re: Need help understanding a performance problem

From: Joel Garry <joel-garry_at_home.com>
Date: 22 Jul 2003 13:09:36 -0700
Message-ID: <91884734.0307221209.47743d54@posting.google.com>

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA703F0906B_at_lnewton.leeds.lfs.co.uk>...
> Morning Harry,
>
> >> Ah, but I could be persuaded to become a Bakkerite, were I only given
> words
> >> of wisdom!!
> What Sybrand is alluding to is the fact that Rich N and Don B are
> disciples of 'ratio based tuning' whereby the database *must* be running
> efficiently if you have a 100% (or close to it) buffer cach hit ratio.
> The fact that Oracle tuning courses teach this as well may have a lot to
> do with it.

Do they really say "must?" I don't have any to hand, but I thought they were just cookbook tuning at worst, which I don't think is all that bad for people with an ordinary job to do, considering the alternatives. In the few times when it still has problems, then they learn more advanced concepts or pay someone, or suffer. We all know it generally isn't the db tuning that gives most of the problems, unless it is really out of whack, like using Oracle defaults.

>
> The rest of the world have long since realised that ratios are a waste
> of time. You can even get a script from Connor McDonald's web site (when
> it comes back online - he has moved back to Australia) which asks for a
> required ratio and with nothing more than a few full table scans manages
> to increase the hit ratio to the requested value or higher. Hencve,
> according to Ricn N or Don B, the database is fully tuned. (Not !)
>
>
> >> IOW, in all seriousness, what's the problem with the increase in
> >> db_block_buffers?
>
> Nothing - provided you don't exceed the point where returns are minimal.
> But don't beleiev that the Hit Ratio being > 90% indicates a tuned
> database :o)

Harry:

The Oracle Press book, Tuning Oracle is a good start, even if it does have a few things that bother some people in this group. To check that you haven't exceeded the minimal point, if FREE is non-zero over an extended period, you are wasting bufferage:

select decode(state,0,'FREE',

                        1,'Read and Modified',
                        2,'Read and Non-Modified',
                        4,'Current Block Read','Other'),count(*)

from x$bh
group by decode(state,0,'FREE',

                        1,'Read and Modified',
                        2,'Read and Non-Modified',
                        4,'Current Block Read','Other')

Some people use a rough rule of thumb to size the SGA before any usage statistics are available, assuming only Oracle db on system, of about 50% real memory.

>
>
> Cheers,
> Norman.
>
> PS. Jitandra Patel is a troll from (I suspect the DB2 news groups) who
> takes great delight in slagging off anyone he feels like. I've been on
> the receiving end myself, but he vanished when I pointed out a few home
> truths in his waffle. Best avoided. He has little or no knowlege of
> Oracle (like my knowlede of DB2) and is best ignored. He also has a
> couple of buddies who back him up from time to time.

--
@home.com is bogus.
http://slate.msn.com/id/2085668/

Received on Tue Jul 22 2003 - 15:09:36 CDT