Re: ** log_buffer

  • From: A Joshi <ajoshi977@xxxxxxxxx>
  • To: lfcerri@xxxxxxxxx
  • Date: Thu, 24 Jul 2008 09:27:21 -0700 (PDT)

Luis,

    Thanks for information and Kevin blog info. I have question about indexes : 



I assume : when data changes : the indexes for the table need to updated too. 


1) On insert all indexes need change and corresponding redo takes place. 2) On
update of table all indexes for the changed column need change. Other indexes 
untouched unworked. 

Or does a update involve all indexes too? Thanks

--- On Thu, 7/24/08, Luis Fernando Cerri <lfcerri@xxxxxxxxx> wrote:
From: Luis Fernando Cerri <lfcerri@xxxxxxxxx>
Subject: Re: ** log_buffer
To: ajoshi977@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx, ajoshi97@xxxxxxxxx, mwf@xxxxxxxx
Date: Thursday, July 24, 2008, 8:40 AM

Hi, Joshi.

Generally reducing commit/rollback ratio may help you.

But instead of just looking to "log file sync" wait, i'd either
take a
look to "log file parallel write" wait event. If the second total
waited time is closer to the first total waited time, then probably
you are waiting for redolog information to be flushed to disk. In this
case, you have two options: reduce redolog information generated by
your processing or improve LGWR throughput.

Nevertheless, I'd like to recommend you a Kevin Closson's blog post:
http://kevinclosson.wordpress.com/2007/07/21/manly-men-only-use-solid-state-disk-for-redo-logging-lgwr-io-is-simple-but-not-lgwr-processing/

It will show you that LGWR can wait for other things than just I/O operations.

Regards,
Cerri


2008/7/23 A Joshi <ajoshi977@xxxxxxxxx>:
> Mark,
>
>    Thanks a lot for the explanation. My main problem is log file sync. I
> have very high log file sync waits (highest wait event) and it waits
almost
> half a second for each which I would like to reduce and I also have high
log
> buffer wait and it is not as high as log file sync but in the top five.
>
>
>
>  Thanks a lot.
>
> --- On Tue, 7/22/08, Mark W. Farnham <mwf@xxxxxxxx> wrote:
>
> From: Mark W. Farnham <mwf@xxxxxxxx>
> Subject: RE: ** log_buffer
> To: ajoshi977@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx, ajoshi97@xxxxxxxxx
> Date: Tuesday, July 22, 2008, 1:37 PM
>
> If you're driving log file syncs as fast as you can at a steady state
of
> load and you can't keep up, then increasing log buffer won't have
a material
> effect on your total throughput. In that case if you need greater
throughput
> you need to find a way to either do less log file syncs per unit work or a
> way to drain them faster.
>
>
>
> If your load (in this case of things that drive log file syncs) is peaked,
> then increasing log buffer has the same characteristics of pushing off
when
> you'll hit log buffer space waits as any other cache. Just because
something
> starts flushing for any given trigger doesn't fundamentally mean more
can't
> be used in parallel with the flushing. That is why these things are
designed
> as "circular" buffers.
>
>
>
> Finding the exact optimal size for log_buffer is usually a fool's
errand
> compared to the cost and availability of memory on today's servers.
Setting
> it a little bigger than you think you need is usually cost effective. Do
> remember that you can't cut out more time from your total throughput
than
> the time of the log buffer space waits plus the side effect costs of the
> context switches those waits cause. Log buffer space waits tend to
introduce
> significant avoidable costs only for batch jobs with large infrequent
> commits since most other jobs will frequently yield for a commit or not
need
> log buffer space at all.
>
>
>
> I hope this helps.
>
>
>
> mwf
>
>
>
> ________________________________
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of A Joshi
> Sent: Tuesday, July 22, 2008 12:01 PM
> To: oracle-l@xxxxxxxxxxxxx; ajoshi97@xxxxxxxxx
> Subject: ** log_buffer
>
>
>
> Hi,
>     Is any specific formula to find the optimum log_buffer size. Any
> considerations. I read that any size above 3MB might not help since it
> flushes at that point anyway. Database version 9i. We are seeing high log
> file sync waits and also log buffer space waits. Thanks
>
>
>
--
//www.freelists.org/webpage/oracle-l


      

Other related posts: