RE: ** log_buffer

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>, <ajoshi97@xxxxxxxxx>
  • Date: Tue, 22 Jul 2008 13:37:57 -0400

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.





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


    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


Other related posts: