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_at_radisys.com>
Date: Fri, 15 Aug 2003 09:49:23 -0800
Message-ID: <F001.005CAEEB.20030815094923@fatcity.com>


Mitchell,

Which portion of the statspack report suggests increasing the cache size?

Maybe you could post that portion here.

The cause for poor performance needs to be located and corrected.

What are the user complaints specifically?

Have you checked to see where your system is spending its time?

This query may prove helpful in that regard:

col event format a35 head 'EVENT NAME'
col total_waits format 999,999,999 head "TOTAL|WAITS" col total_timeouts format 999,999,999 head "TOTAL|TIMEOUTS" col time_waited format 999,999,999 head "TIME|WAITED|SECONDS" col average_wait format 99999 head "AVG|WAIT|100ths"

set line 150
set trimspool on

select

        event,
        total_waits,
        total_timeouts,
        time_waited/100 time_waited,
        average_wait

from v$system_event
order by time_waited
/

ttitle off

Jared

Mitchell <mitchell.lee_at_rogers.com>
Sent by: ml-errors_at_fatcity.com
 08/15/2003 09:24 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: SGA


Hi Jared

I have reset to previous value and restarted the database anyway. Since I
have 8G Memory and I may set SGA more than 3G. Actually I did at our AIX SP that set SGA total up to 5G of 8G memory.

Anyway I found a solution on metalink ( Note 115753.1 and 1028623.6. ) to set SGA to higher. You may be interest to read it.

You are right it is best to find a root cause of poor performance. The reason I want to increases db buffer is our customer complain slow on our database and Statpack report suggest to increase it as well

Thanks
Mitchell

> 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: 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: 
  INET: Jared.Still_at_radisys.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 - 12:49:23 CDT

Original text of this message

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