Sizing of the redo logs has a large impact on database performance because the redo log size has a heavy impact on the behavior of DBW and ARCH processes.  Generally, large redo logs redo provide better performance by decreasing the amount of check points.   However, the parameter FAST_START_MTTR_TARGET is used to limit the amount of time required to recover the database and more frequent checkpoints make for a shorter recovery time, thus it is a balance between recovery time and performance.

 

V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE shows the optimal size of the redo log file.  This info is also available in Redo Log Group page of OEM.  As a general rule, redo logs should switch once every 20 minutes.  NOTE:  the initialization parameter FAST_START_MTTR_TARGET must be set to have this column populated.

 

SQL> select optimal_logfile_size from v$instance_recovery;

 

OPTIMAL_LOGFILE_SIZE

——————–

               41425

 

SQL>

 

 

Larry Catt

OCP