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:

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:

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

3 Comments

  1. caesar dutta

    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

      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.

Leave a Reply to narsimulu Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.