Oracle Performance Tuning Tips

Use raw log files

There are two important performance problems with buffered log file I/O.

Buffered log file I/O suffers from partial block writes. Because redo writes address an arbitrary number of log blocks (see redo write size) and log blocks are small relative to the size of file system buffers (see log block size) it is normal that the last log block of a redo write does not align with the end of a file system buffer. Therefore, unless the target file system block from the old image of the log file is already in cache, the operating system must first read that block from disk before the new redo can be copied into part of its file system buffer. Such a preliminary read from the log file is needed for the majority of buffered redo writes.

If a disk array with a large cache and an effective prefetching algorithm is used for the log files, then the preliminary read will only take about 2 milliseconds, and will not otherwise delay the redo write. However, if both the preliminary read and the redo write require physical disk service, there must be an additional delay equivalent to a full rotation of the disk before the write can be serviced.
Buffered log file I/O also suffers from I/O fragmentation. When redo writes address more than one file system buffer, the physical writes for each buffer are not queued in parallel, and therefore cannot be processed seamlessly by the hardware. Instead, the component physical writes are performed serially, and a full rotational latency is sustained between each pair of writes.

Both of these problems can be prevented by using a file system direct I/O facility for the log files, or by using raw log files. Raw I/O is normally preferable to direct I/O, because it enables more efficient asynchronous I/O.

LGWR needs asynchronous I/O to parallelize writes to multiple log file members (see redo write multiplexing) and to overlap redo writes when transactions are committed in quick succession. Hardware mirroring can be used in preference to redo log multiplexing to avoid serial writes to multiple log file members, and indeed such hardware mirroring is recommended to minimize the CPU cost of redo writes. However, asynchronous I/O is nevertheless needed to overlap consecutive redo writes, because LGWR I/O slaves do not do so consistently. Raw I/O (or Quick I/O) makes kernelized asynchronous I/O possible, whereas otherwise LGWR would have to rely on threaded asynchronous I/O or LGWR I/O slaves.

Partial block writes, I/O fragmentation and synchronous I/O all have a major impact on LGWR's I/O performance, over and above the performance and scalability penalty that buffered I/O carries in any case. However, because LGWR is an Oracle background process and does much of its work in the background, poor LGWR I/O performance might not be noticed except during log file sync waits after commits. This can nevertheless be an important and difficult wait event to tune, particularly in OLTP environments, and the strategy of switching to raw log file I/O has proved to be very effective on many occasions. This strategy can even be used in environments that do not have any facility to backup raw disk, because online log files should not be backed up anyway.

In some instances, poor LGWR I/O performance also results in considerable log buffer space waits despite the use of a large log buffer. This occurs when there is an extended burst of redo generation, and LGWR is just not able to keep up. In such instances switching to raw log file I/O typically eliminates the log buffer space waits immediately, and raises the ceiling on the sustainable rate of redo generation dramatically.