Oracle Performance Tuning Tips

Use three or more log files

Oracle requires that every redo thread have at least two log files. This number is sufficient for databases operating in NOARCHIVELOG mode and for instances that never fill a whole log file very quickly and where manual log switches are exceptional. Most instances, however, need three or more log files. Otherwise, if there are only two log files, A and B, and if the backup tool/script tries to switch from B to A shortly after a log switch from A to B has occurred normally, or if a burst of redo generation causes a log switch from A to B and then quickly fills the whole of log file B, then ARCn may not have had long enough to finish archiving log file A.

There are lots of reasons why LGWR can fill a log file more quickly than ARCn can archive a file of the same size. LGWR reads from memory, whereas ARCn has to read from disk. Also LGWR can write to raw log files, whereas ARCn must write to a file system. This often means that LGWR can perform asynchronous writes, but ARCn cannot. Also LGWR overwrites an existing file, whereas ARCn must continually extend the file it is writing. This involves significant file system space management overheads, which more than double the time taken to write the file. So when the rate of redo generation is fast enough, it is easy for LGWR to write faster than ARCn can copy, even if ARCn is just copying the log files to a single archive destination. ARCn's workload may be even greater if it needs to archive the log files to multiple destinations. This is because each log file is copied to all the archive destinations by a single ARCn process, even if multiple ARCn processes are available.

If ARCn has not finished archiving a log file before LGWR attempts to switch into that file, then the log switch will block and no further redo generation will be possible until the log switch completes. This is of course disastrous for transactional performance. Processes needing to generate redo wait on the log file switch (archiving needed) event, and a message like the following one is written to the alert.log file.

Thread 1 cannot allocate new log, sequence 82324 
All online logs needed archiving

Three or more log files are needed to avert such incidents. Increasing the number of log files from 2 to 3 doubles the archival window. For instances that must support sustained fast redo generation, or that archive the log files to multiple destinations, four or more log files may be required. However, given that each log file should be placed on a dedicated mirrored pair of disks unless using a cached disk array it might be better to increase the stripe breadth of the archive destination area to improve the archival speed, rather than to use a larger number of online log file disks to increase the archival window.