Friday, January 15, 2010

Oracle 10g REDO LOGS sizing

The size of the redo log files can influence performance, because the behavior of
the dbw and arc processes depend on their sizes.
Larger redo log files provide better performance but instance recovery time is increased. Sou you must find a good balance depending the type of your system and your instance recovery time needed.

Also be careful that undersized log files increase checkpoint activity and increase CPU usage.Checkpoint frequency is affected by several factors, including log file size and
the setting of the FAST_START_MTTR_TARGET.

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 size of the log files should be large enough to avoid
additional checkpointing due to under sized log files.

In Oracle 10g the redo logfile size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery or in the EM Dbconsole under Administration Page under Storage on the Redo Log Groups Link on the Actions List.

In order to enable this feature you must set the parameter "fast_start_mttr_target" in order to activate the advisory and the population of the column optimal_logfile_size.

The key columns in v$instance_recovery view are :

ACTUAL_REDO_BLKS
The current actual number of redo blocks required for recovery.

TARGET_MTTR
Effective MTTR (mean time to recover) target value in seconds. The TARGET_MTTR value is calculated based on the value of the FAST_START_MTTR_TARGET parameter and is usually an approximation of the parameter's value. However, if the FAST_START_MTTR_TARGET parameter value is very small (for example, one second), or very large (for example, 3600 seconds), the calculation will produce a target value dictated by system limitations. In such cases, the TARGET_MTTR value will be the shortest calculated time, or the longest calculated time that recovery is expected to take.

ESTIMATED_MTTR
The current estimated mean time to recover (MTTR) based on the number of dirty buffers and log blocks (0 if FAST_START_MTTR_TARGET is not specified). Basically, this value tells you how long you could expect recovery to take based on the work your system is doing right now.

OPTIMAL_LOGFILE_SIZE
The recommended optimal redolog file size for the current setting of FAST_START_MTTR_TARGET.

select ACTUAL_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,OPTIMAL_LOGFILE_SIZE
from v$instance_recovery;

The target is to reduce ACTUAL_REDO_BLKS by setting an appropriate value of FAST_START_MTTR_TARGET and finding the OPTIMAL_LOGFILE_SIZE for the work your system does.

1 comment: