Oracle-L: RE: SGA

From: Hately, Mike (LogicaCMG) <mike.hately_at_nedl.co.uk>
Date: Tue, 19 Aug 2003 01:49:24 -0800
Message-ID: <F001.005CB6C4.20030819014924@fatcity.com>

No, time waited is in 100ths of a second so you have divide to get the number of seconds.

regards,
Mike Hately

-----Original Message-----
Sent: 19 August 2003 07:04
To: Multiple recipients of list ORACLE-L

Shouldn't time_waited/100 be time_waited*100?

Regards
Naveen

-----Original Message-----
Sent: Friday, August 15, 2003 11:19 PM
To: Multiple recipients of list ORACLE-L

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:

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

Original Message ----- To: <ORACLE-L_at_fatcity.com> Cc: <mitchell.lee_at_rogers.com> Sent: Friday, August 15, 2003 10:42 AM

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







DISCLAIMER:
This message (including attachment if any) is confidential and may be
privileged. Before opening attachments please check them for viruses and
defects. MindTree Consulting Private Limited (MindTree) will not be
responsible for any viruses or defects or any forwarded attachments
emanating either from within MindTree or outside. If you have received this
message by mistake please notify the sender by return e-mail and delete this
message from your system. Any unauthorized use or dissemination of this
message in whole or in part is strictly prohibited. Please note that e-mails
are susceptible to change and MindTree shall not be liable for any improper,
untimely or incomplete transmission. 



********************************************************************************************
E mail Disclaimer

You agree that you have read and understood this disclaimer and you agree to be bound by its terms.

The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only.  If you have received this  e-mail in error please notify the originator.    

This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on.

No warranty of any kind is given in respect of any information contained in this   e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it.

CE Electric UK Funding Company
Lloyds Court
78 Grey Street
Newcastle upon Tyne
NE1 6AF
Registered in England and Wales: Number 3476201

********************************************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hately, Mike (LogicaCMG)
  INET: mike.hately_at_nedl.co.uk

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 Tue Aug 19 2003 - 04:49:24 CDT