Re: log_buffer size

Hi Chuan,

Either chance X_$KCCLE to X$KCCLE and run as SYS, or use the create_xviews.sql script from the Ixora web site to create the X_$ view.

@   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>

Hi, Steve,

   To my DB, the sync cost ratio is very bad, it's about 44.1. I could not get the "write threshold" value because x_$kccle does not exist. In this case, can I just simple increase the log_buffer size?

Thanks,

Steve Adams <steve.adams_at_ixora.com.au> wrote in message news:39cb54b0.168957297_at_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 Wed Sep 27 2000 - 02:34:53 CDT