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: log_buffer size

Re: log_buffer size

From: Chuan Zhang <chuan_at_asiaonline.net>
Date: Thu, 28 Sep 2000 10:37:55 +1000
Message-ID: <PyvA5.3$B52.48176@news.interact.net.au>

Hi, Steve,

  I have run your srcipts against our production DB.

I got the following results:

SVRMGR> @log_sync_cost_ratio.sql
SYNC_COST_



17.8225535
1 row selected.
SVRMGR> @write_size_threshold
WRITE_SIZE THRESHOLD
---------- ----------

      6656 54784
1 row selected.
SVRMGR> @dbwn_sync_waits
SYNC_WAITS


     1.09%

At this moment, the log_buffer is 160k. The dbwr_io_slaves = 0, disk_asynch_io=true. The checkpoint is controlled by log_checkpoint_interval=10,000.

In our utlbstat/utlestat report, the log file sync wait is very high.

I have read through Ixora Q&A about redo log tuning. But I could not figure out my problem. Besides tunning the application commit and O/S level tunnig for redolog files, what anything else I can do ?

Looking forward to hearing from you because the day after next tomorrow I got a chance to reconfigure our production DB.

BTW, I am very interested in your book "Oracle 8i Internal Services". If I buy it through Amazon, how long can I get it? I am living in Canberra, Australia.

Thanks,

The following is the Wait statistics from utlbstat/utlestat report, sorry it is so mess.

What the negetive number is?

Thanks very much

Chuan Zhang

Steve Adams <steve.adams_at_ixora.com.au> wrote in message news:39d1a2c6.943426_at_nsw.nnrp.telstra.net...
> 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 - 19:37:55 CDT

Original text of this message

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