Re: log_buffer size

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 22 Sep 2000 12:59:01 GMT
Message-ID: <39cb54b0.168957297@nsw.nnrp.telstra.net>

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. Received on Fri Sep 22 2000 - 07:59:01 CDT