REDO LOGS SIZING ADVISORY

Why redo log size is important

• The size of the redo log files can influence performance, Too small, and the frequent log switches tie-up the LGWR, ARCH and DBWR background processes.Undersized log files increase checkpoint activity and increase CPU usage.
  

How to Tune Checkpoint

• Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter.There is an detail articale about tunning checkpoint (http://serdarturgut.blogspot.com/2007/09/tune-checkpoint.html)
  


• you can check it
  show parameter fast_start_mttr_target

How to check redo size and redo switch

• Prior to 10g, it was not possible to provide a specific size recommendation for redo log files. Generally it is recommended to size your redo log file in a way that Oracle performs a log switch every 15 to 30 minutes.
  


• You can check log switches from v$log_history in a day or per hour etc.
  select * from v$log_history where first_time >trunc(sysdate);
  


• You can check the log size from v$log
  
  select GROUP# , BYTES,archived,status from v$log;
  GROUP# BYTES ARC STATUS
  --------- ------------------------ --- ----------------
  1 10,485,760 YES ACTIVE
  2 10,485,760 NO CURRENT
  3 10,485,760 YES ACTIVE
  4 10,485,760 YES ACTIVE


• Note that ARCHIVE_LAG_TARGET can also be used to force a log switch after the specified amount of time elapses.
  show parameter archive_lag_target
  

How to set and Check the reso size advisory

• The redo logfile sizing advisory is a new feature in 10g where in the redo logfile size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery.This feature require setting the parameter "fast_start_mttr_target" for the advisory to take effect and populate the column optimal_logfile_size.
  select ACTUAL_REDO_BLKS ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR, OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES
  from v$instance_recovery;
  ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE CKPT_BLOCK_WRITES
  ---------------- ---------------- ----------- -------------- -------------------- -----------------
  35190 55260 31 24 140 4144049
  


• The value for optimal_logfile_size is expressed in megabytes (For this example optimal size is 140m ) and it changes frequently, based on the DML load on your database.