Spotlight on Oracle – Redo Log Configuration

Redo log configuration

When a transaction is committed, a physical write to the redo log file must occur. The write must complete before the commit call returns control to the user. Hence, redo log writes can provide a limit to throughput of update-intensive applications.

Redo log I/O is optimized if the log is on a dedicated device and there is no contention for the device. If this is achieved, the disk head is already in the correct position when the commit is issued, and write time is minimized (the disk won’t need to seek).

Because the log writes are sequential and are performed by the Logwriter processes only, there is little advantage in striping. Since LGWR is write-only to these devices, the performance degradation caused by RAID 5 is likely to be most significant, even if the volume is dedicated to redo logs (because of contention with the archiver process).

To insure against any loss of data in the event of a media failure, it is essential that the redo logs are mirrored. Oracle provides a software mirroring capability (redo log multiplexing), although hardware mirroring (RAID 1) is probably more efficient.

Because switching between redo logs results in a database checkpoint, and because a log cannot be reused until that checkpoint is completed, large and numerous logs can result in better throughput. By increasing the number of logs, you reduce the possibility that a log is required for reuse before its checkpoint is complete. By increasing the size of the logs, you reduce the number of checkpoints that must occur.

The optimal size for your redo logs depends on your transaction rate. You should size the logs so that switches do not occur too rapidly. Since you usually allocate dedicated devices for redo logs, there is likely to be substantial disk capacity available for logs. It is often easiest to over-configure the log size and number initially. Log sizes of 64 to 256 megabytes are not uncommon. Configuring from 10 to 20 redo logs is also not unusual.