DETERMINE OPTIMAL SIZE FOR REDO LOGS

DETERMINE OPTIMAL SIZE FOR REDO LOGS

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin.htm

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs to be at least this size.

Oracle V$INSTANCE_RECOVERY View:

We need to query the V$INSTANCE_RECOVERY view in order to use the Redo Logfile Size Advisor. Oracle V$INSTANCE_RECOVERY monitors the mechanisms available to users to limit recovery I/O. These include

•Setting LOG_CHECKPOINT_TIMEOUT parameter.
•Setting LOG_CHECKPOINT_INTERVAL parameter.
•Setting FAST_START_MTTR_TARGET parameter.
•Setting the size of the smallest redo log.


Note, however, that the redo log file size affects the MTTR. In some cases, you may be able to refine your choice of the optimal FAST_START_MTTR_TARGET value by re-running the MTTR Advisor with your suggested optimal log file size.

SQL> select OPTIMAL_LOGFILE_SIZE from V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE
--------------------
92

SQL> show parameter mttr;

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
fast_start_mttr_target integer
180
SQL>


SELECT TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY;