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.
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.
© Ixora Pty Ltd. All rights reserved.