Shouldn't time_waited/100 be time_waited*100?
Regards
Naveen
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, August 15, 2003 11:19 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: SGA
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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]08/15/2003 09:24 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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.
