Redo logs contain details of transactions that may not yet have been written to the data files. The primary purpose of the redo logs is to allow for the recovery of the database in the event of a system or database failure.
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 will not need to seek).
Because the log writes are sequential and are performed by the Log Writer 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.
Optimizing archiving
Archived logs are copies of online redo logs. They can be used to recover a database to point of failure or to another point in time after a backup has been restored. Archive logging is also required if online backups are desired.
Once a redo log file is filled, Oracle moves to the next log file. The archiver process (ARCH) copies the recently-filled log to an alternate location. If the archiver reads from a log on the same physical device as the current log being written, the sequential writes of the log writer are disrupted. If the log writer falls sufficiently behind, the database can stall (since a log file cannot be reused until it has been archived).
Therefore, it is important to optimize the performance of the archiver. Contention between the archiver and the log writer can be minimized by alternating redo logs over two devices. The redo log writer can then write to one device, while the archiver is reading from the other device. Since the archiver must be capable of writing at least as fast as the log writer, the archive destination should either be a dedicated device, or a dedicated set of disks in a RAID 0+1 (mirrored and striped) configuration.
Log file wait events
Just as Oracle sessions must wait for database file I/O, they must also wait for log file I/O. Such waits occur whenever a COMMIT statement causes a write to the redo log. The session issuing the COMMIT waits on the log file sync event. When the log writer issues the I/O request, it waits on the log file parallel write event.
Both these wait events are inevitable, and often account for between 10-20% of total non-idle wait times in a healthy database.
The average wait time for a log file parallel write is an important measure. It indicates how quickly the log writer process can flush the redo buffer. It is a good indicator of the efficiency of the redo log device. Values below one-hundredth of a second are good, and values of up to five-hundredths of a second are not unusual. Values above this range may indicate contention for the redo log device.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
