Re: log_buffer size

I think that I have the answer - the app running in this db is mainly OLTP, developed with Forms, but some daily batch routines was written in C, and that routines make very long and complexes tasks, commiting just in the end of the work. This way, it generates a burst of redo when commits, occupies a lot of resources and make dbwr work like a mad dog, writing a whole bunch of redo bytes (normally the dbwr is always do-nothing,mainly bcause I setted log_checkpoint_interval bigger than redo-files and log_checkpoint_timeout to 0).

Well, nothing new here - I will dig a little, but the only way is to fix the app, as always :(

Many thanks for your answer, Steve !

[]s

    J. Laurindo Chiappa

In article <39cb54b0.168957297_at_nsw.nnrp.telstra.net>,   steve.adams_at_ixora.com.au (Steve Adams) wrote:
> Hi Laurindo,
>
> It means that you commit so frequently (or generate redo so slowly)
 that the
> background write threshold (1/3 of log_buffer) is seldom the trigger
 for a LGWR
> write. Also 'log file sync' waits are a little faster than 'log file
 parallel
> write' waits, which means that for some syncs LGWR had already begun
 the write
> when the process began to wait for it. Given the fact that background
 writes are
> relatively rare, that suggests that there are DBWn syncs. You can
 check that
> with the script "dbwn_sync_waits.sql" at
> http://www.ixora.com.au/scripts/redo_log.htm#dbwn_sync_waits. DBWn
 syncs could
> be due to overzealous checkpointing, or too small a buffer cache.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
> @
> @ Going to OpenWorld?
> @ Catch the Ixora performance tuning seminar too!
> @ See http://www.ixora.com.au/seminars/ for details.
>
> -----Original Message-----
> From: chiappa_at_my-deja.com
>
> Steve, excuse me but I´m just curious about your answer : in my db,
 the
> sync cost ratio is well under 1.5, but 'average log write size' is
 very
> different of 'background write threshold'. What it means ?
>
> []s
> J. Laurindo Chiappa
>
> --------------------- cut here ----------------------------
> Avgerage Log Background
> Write Size Write Theshold
> -------------- --------------
> 5632 87552
>
> Sync Cost Ratio
> ---------------
> 0.95
>
> -----------------------------------------------------------------
> In article <39cb197f.153804939_at_nsw.nnrp.telstra.net>,
> steve.adams_at_ixora.com.au (Steve Adams) wrote:
> > Hi Chuan,
> >
> > Have a look at the script "lgwr_stats.sql" at
> > http://www.ixora.com.au/scripts/redo_log.htm. If the 'sync cost
 ratio' reported
> > by that script is approximately 1, then you can do nothing other
 than
 attempt to
> > reduce your commit frequency and optimize log file writes at the
 operating
> > system level.
> >
> > If the ratio is higher than 1.5, then you may have an instance
 tuning
 problem
> > worth investigating. If the 'log_buffer' is too small, it can
 increase 'log file
> > sync' times indirectly via 'redo allocation' latch contention. If
 so,
 in
> > addition to the latch contention itself in V$LATCH, the above script
 would show
> > that the 'average log write size' is of the same order of magnitude
 as the
> > 'background write threshold'. If not, then increasing
 the 'log_buffer' size is
> > not likely to have a significant impact, but if anything it would in
 fact
> > increase the average 'log file sync' time rather than reduce it.
 That
 is
> > explained in tip on the Ixora web site called "Tuning the Log Buffer
 Size" at
> > http://www.ixora.com.au/tips/tuning/log_buffer_size.htm.
> >
> > @ Regards,
> > @ Steve Adams
> > @ http://www.ixora.com.au/
> > @ http://www.christianity.net.au/
> > @
> > @ Going to OpenWorld?
> > @ Catch the Ixora performance tuning seminar too!
> > @ See http://www.ixora.com.au/seminars/ for details.
> >
> > -----Original Message-----
> > From: "Chuan Zhang" <chuan_at_asiaonline.net>
> >
> > At present, our production (Oracle EE 8.0.6, solaris 2.7) is using
 156K for
> > 'log_buffer'. The performance tunning for log buffer is ok. but
 the 'log file
> > sync' is bad. I cannot do anything to relocate the redo log files. I
 think
> > it might improve the performance to increase the log buffer or
 tuning
 the
> > log checkpoint interval.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 22 2000 - 10:34:55 CDT