Tuning the Oracle Log Buffer Size

Just add

Although "log file sync" waits are generally thought to result from slow LGWR writes, but these are waits reported by the sessions that have been doing transactions -- not, as such, waits reported by LGWR itself.

And in many large OLTP system, like payroll, B2B, large # of "log file sync" does happen due to its application design.

According to this article, it is tuning the log buffer sizing via "_log_io_size"; I can’t agree with it, modifying on this setting might decrease wait # of "log file sync" but impact on transactions are unpredictable.

Tuning the Log Buffer Size is a matter of careful balance, and a careful setting of hidden _log_io_size parameter is just a option which need very professional guidlines to follow:

A relatively small _log_io_size setting to minimize log file sync waits and reduce their duration.

However, _log_io_size must not be so small as to keep LGWR unduly active. LGWR uses the redo allocation latch both before and after each write. If LGWR is over active, then there is increased risk of redo allocation latch contention. An over active LGWR also wastes CPU time by performing multiple small writes, and by running more often which involves extra context switches. This can impact LGWR's operating system scheduling priority, which in turn degrades LGWR performance even further.

For raw log files, or file system based log files with direct I/O enabled, the optimal setting for _log_io_size is normally just below the maximum physical I/O size supported by the operating system (device driver). For file system based log files without direct I/O, a smaller setting normally works better.

Note that the _log_io_size setting must be specified in log blocks, whereas the log_buffer parameter is set in bytes. Note further that setting _log_io_size is no excuse for having a wastefully large log_buffer setting. It is very rare to require a log buffer more than a few hundred kilobytes in size.

It should be a good idea that you can discuss with the author who should be Oracle expert through the link above.

Applications that insert, modify, or delete large volumes of data usually need to change the default log buffer size. The log buffer is small compared with the total SGA size, and a modestly sized log buffer can significantly enhance throughput on systems that perform many updates.

A reasonable first estimate for such systems is to the default value, which is:

MAX(0.5M, (128K * number of cpus))

On most systems, sizing the log buffer larger than 1M does not provide any performance benefit. Increasing the log buffer size does not have any negative implications on performance or recoverability. It merely uses extra memory.