Appropriate sizing of Online REDO Logs

Appropriate sizing of Online REDO Logs

Appropriate sizing of online redo logs to control the frequency of log switches can pay great dividends, especially on High Transaction systems. It becomes further more critical for Archive logged databases, as the redo log switch also triggers archiving activity which can be resource intensive.

In order to size the redo logs correctly, we first need to understand how often log switches are taking place on the database in question. The query given in the listing below can be used to obtain log switch information for the past 7 days.

select to_char((first_time), ‘DD-MON-YYYY’) “DAY”, to_char((first_time), ‘HH24:MI’) “TIME”, first_change# “START_SCN”,next_change# “END_SCN” from v$log_history where to_char((first_time), ‘DD-MON-YYYY’) > (sysdate-7) order by “DAY” DESC, “TIME” DESC

Once we identify the frequency of log switches, we need to size our online redo logs keeping a in mind a general rule of thumb that says,

‘For high transaction databases, a log switch should not occur for more than 3-4 times or once in approximately 20 min’