Optimum Size of The Online Redo Log Files

The Oracle Database Performance Tuning Guide recommends switching logs at most once every twenty minutes. If our online redo logs switch once every 10 minus during peak hours, our redo logs should be increased to 2 times larger then their current size.

Here’s a simple query for calculating recommended size of redo log files:

SELECT
(SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES)
FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 3
AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
&START_OF_PEAK_HOURS AND &END_OF_PEAK_HOURS);

SELECT

(SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",

ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES)

FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"

FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD

FROM V$ARCHIVED_LOG

WHERE FIRST_TIME > SYSDATE - 3

AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN

&START_OF_PEAK_HOURS AND &END_OF_PEAK_HOURS);

With Oracle 10g, we can also use “Redo Logfile Size Advisor”. This advisory utility allows you to specify your optimal mean time to recovery (MTTR) interval and calculates the optimal redo log size. We can query V$INSTANCE_RECOVERY to see the optimal_logfile_size:

ALTER SYSTEM SET FAST_START_MTTR_TARGET=100;

SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE
--------------------
2285

ALTER SYSTEM SET FAST_START_MTTR_TARGET=400;

SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE
--------------------
11230

ALTER SYSTEM SET FAST_START_MTTR_TARGET=100;

SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE

--------------------

2285

ALTER SYSTEM SET FAST_START_MTTR_TARGET=400;

SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

OPTIMAL_LOGFILE_SIZE

--------------------

11230

Please share

3 Comments

caesar dutta

July 2, 2015 Reply

Dear Sir:

What does “20” stands for in ROUND((20 / AVERAGE_PERIOD).

This is a nice blog but I think thread# should be considered for RAC database. I have re-written your query as
SELECT v1.thread#, v3.curr_val “Current Size”,
round((20/ v1.average_period) * (v2.reco1)/ 1024 / 1024, 2) AS “Recommended Size (MB)”
FROM (
SELECT thread#, AVG((NEXT_TIME – FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE – 3
AND TO_CHAR(FIRST_TIME, ‘HH24:MI’) >= ’09:00′
AND TO_CHAR(FIRST_TIME, ‘HH24:MI’) <= ’15:00′
group by thread#
)V1,
(
SELECT thread#, AVG(BYTES) reco1 FROM V$LOG group by thread#
)V2,
(
SELECT thread#, ROUND(AVG(BYTES) / 1024 / 1024, 2) curr_val FROM V$LOG group by thread#
)V3
where v1.thread# = v2.thread#
and v2.thread# = v3.thread#

regards,

Caesar
- Gokhan Atil
  
  July 8, 2015
  
  Hi Caesar,
  
  It’s 20 minutes. I assume that, if you don’t have a very busy OLTP database, log switch on very 20 minutes is ideal. So I try to find the redo log size which will help your redo logs switch on every 20 minutes.
narsimulu

August 17, 2018 Reply

what is the recommended redolog size for log sequence change