RE: ** log_buffer

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_at_rsiz.com> wrote:

From: Mark W. Farnham <mwf_at_rsiz.com>
Subject: RE: ** log_buffer
To: ajoshi977_at_yahoo.com, oracle-l_at_freelists.org, ajoshi97_at_yahoo.com 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of A Joshi Sent: Tuesday, July 22, 2008 12:01 PM
To: oracle-l_at_freelists.org; ajoshi97_at_yahoo.com 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
 

--
http://www.freelists.org/webpage/oracle-l