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

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance Tuning

Re: Performance Tuning

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Thu, 7 Oct 1999 22:49:17 +0200
Message-ID: <939329454.17357.0.pluto.d4ee154e@news.demon.nl>


Hi Stephan,

A few quick remarks:

1 sort_area_size: 16k, way too low. Start at 262144 (256k) with a possible increase to 1048576 (1M)
2
log_buffer: way too big

Here is a recent post by Thomas Kyte (tkyte_at_us.oracle.com) addressing this issue

From: Thomas Kyte <tkyte_at_us.oracle.com> Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server Sent: Sunday, September 26, 1999 8:00 PM Subject: Re: log_buffer settings

> A copy of this was sent to "Steve Perry" <sperry_at_sprynet.com>
> (if that email address didn't require changing)
> On Sun, 26 Sep 1999 10:52:45 -0700, you wrote:
>
> >Can anyone help out with "realistic" settings for the log_buffer parm, as
> >well as anything else I might want to check?
> >I have kept increasing the log_buffer parm to 16meg now, but still keep
> >seeing the waits below. I changed the log_simultaneous_copies to 16 (2 x
> >#cpu's), but nothing seems to help. The below was from a 2 hour window
when
> >normal activity was taking place. It's an OLTP system (400 concurrent
> >users), Oracle 7.3.4, aix 4.3.2 (4 gig of ram), sga of about 700 meg, 250
> >meg of shared pool. The Oracle manuals state that increasing the
log_buffer
> >beyond 1 meg does no good, but I've heard otherwise. It hasn't made any
> >difference for me... There must be something I'm missing.
> >
>
> reduce that log buffer size, get it back down into KILOBYTES, not
megabytes.
>
> increasing the log buffer will not help (and can in fact hurt).
>
> the redo log space requests are not waits for space in the redo log
buffer -
> they are waits for space in the online redo logs themselves. (some
documentation
> erroneously tells you to increase the log_buffer -- it is wrong,
increasing the
> log_buffer -- as you have seen -- will have no effect on this).
>
> Increase the size and or number of your online redo logs. Look in your
alert.ora
> file for messages to the effect of "cannot allocate new log". They will
be
> either because "checkpoint not complete" (add more log for this) or
because the
> archiver hasn't finished (tune your redo disks to remove contention
between lgwr
> and arch and perhaps add more redo).
>
>
> >from utlstat report (system statistics)
> >
> >Statistic Total Per Transaction
Per
> >Logon Per Second
> >redo log space requests 10,998 .03
> >38.73 1.19
> >redo log space wait time 1,070,620 3.38 3,769.79
> >115.96
> >
> >Thanks,
> >Steve
> >
> >
>
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation

Hth,

--
Sybrand Bakker, Oracle DBA Received on Thu Oct 07 1999 - 15:49:17 CDT

Original text of this message

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