How does one tune the Redo Log Buffer?

The size of the Redo Log Buffer is determined by the LOG_BUFFER parameter in your SPFILE/INIT.ORA file. The default setting is normally 512 KB or (128 KB * CPU_COUNT), whichever is greater. This is a static parameter and its size cannot be modified after instance startup.

SQL> show parameters log_buffer

NAME                                 TYPE        value
------------------------------------ ----------- ------------------------------
log_buffer                           integer     262144

When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:

• Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
• Every 3 seconds; or
• When a DBWn process writes modified buffers to disk (checkpoint).

Larger LOG_BUFFER values reduce log file I/O, but may increase the time OLTP users have to wait for write operations to complete. In general, values between the default and 1 to 3MB are optimal. However, you may want to make it bigger to accommodate bulk data loading, or to accommodate a system with fast CPUs and slow disks. Nevertheless, if you set this parameter to a value beyond 10M, you should think twice about what you are doing.

SQL> SELECT name, value
  2    FROM SYS.v_$sysstat
  3   WHERE NAME in ('redo buffer allocation retries',
  4                  'redo log space wait time');

NAME                                                                  value
---------------------------------------------------------------- ----------
redo buffer allocation retries                                            3
redo log space wait time                                                  0

Statistic "REDO BUFFER ALLOCATION RETRIES" shows the number of times a user process waited for space in the redo log buffer. This value is cumulative, so monitor it over a period of time while your application is running. If this value is continuously increasing, consider increasing your LOG_BUFFER (but only if you do not see checkpointing and archiving problems).

"REDO LOG SPACE WAIT TIME" shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer. If this value is low, your log buffer size is most likely adequate.