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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SGA

Re: SGA

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 15 Aug 2003 07:29:29 -0800
Message-ID: <F001.005CAEBD.20030815072929@fatcity.com>


Was a low cache hit ratio the only 'problem'?

Were jobs taking longer than normal?

Were users complaining of a slow system?

Did your average response time shoot up dramatically?

I'm afraid you may have succumbed to the dreaded disease, CTD, or Compulsive Tuning Disorder.

This is the urge to tweak database parameters due to some arbitrary threshold. even though everything seems to be working fine.

It isn't your fault, as a number of publications in the past, including Oracle's, have taught that having a block cache hit ratio, or BCHR, lower than 90 or 95% meant that your system was performing poorly.

The first thing to do is set your db_block_buffers size back to a value that allows you to start the database.

Then head on over to http://www.hotsos.com and register for the site. Click on 'Knowledge On-Line' at the top of the page, and get the paper "Why 99% Database Buffer  Cache Hit Ratio is NOT Ok".

There are other useful papers there as well, and indeed many other sites where you can find useful information about BCHR. This will get you started.

Of course, someone (Connor McDonald I think) wrote a nice utility that will increase your BCHR for you, but I don't think you want to use it in production. :) It's chief purpose is to underscore the futility of trying to tune via the block cache hit ratio.

HTH Jared

On Thu, 2003-08-14 at 16:19, Mitchell wrote:
> Hi DBAs
>
> We have Sun Sparc system (Sun Fire 880) with 8G memeory. We has setting
> /etc/system to the max and data buffer catch 1000m and total SGA 1745MB on
> our . Oracle 8.1.7.3.0 Server. Since Dat Buffer Cache hit ration
> lower then 50% for last a few days, So We decide to increase another 400MB
> to DB buffer. We faile to restart instance then, reside to 1200M, still
> failed. I have no clue at all for why. (I have checked the Metalink, no
> solution but redece SGA size). Anybody has any idea?
>
> Thanks in advance.
>
> Mitchell
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mitchell
> INET: mitchell.lee_at_rogers.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Aug 15 2003 - 10:29:29 CDT

Original text of this message

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