Redo Log File Sizings

The size of the redo log files influences performance because the database writer and archiver processes depend on the redo log sizes. Larger redo log files tend to provide better performance along with appropriate expected recovery time. If the redo log files are small, they increase checkpoint activity and increase CPU usage. Smaller redo logs lessen the performance, because during log switching, all operations are frozen in the database and all sessions may wait for log switch completion.

Checkpoint frequency is influenced by log-file size and by the FAST_START_MTTR_TARGET parameter, among several factors. If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle automatically tries to checkpoint as frequently as necessary. Under this condition, the log files should be large enough to avoid additional checkpointing due to smaller log files.

The redo log file size can be tuned using recommendations mentioned in the OPTIMAL_LOGFILE_SIZE column of V$INSTANCE_RECOVERY. If the FAST_START_MTTR_TARGET has such a small value to do a recovery within its usual time frame, then the TARGET_MTTR field of V$INSTANCE_RECOVERY contains the effective MTTR target value, which is larger than FAST_START_MTTR_TARGET. If FAST_START_MTTR_TARGET is set to such a high value to make recovery possible in a very bad database crash, then TARGET_MTTR contains the estimated MTTR value for the worst-case scenarios.