How to calculate redo log size?

Question

I am trying to figure out if the current redo log size I have right now is optimal. Here is what I have done:

1. I used the Oracle documentation to find most of this information: http://www.oracle.com/technetwork/database/availability/async-2587521.pdf

2. Using SQL I used the below query

    select thread#,sequence#,blocks*block_size/1024/1024 "MB",(next_time-first_time)*86400 "sec", 
    (blocks*block_size/1024/1024)/((next_time-first_time)*86400) "MB/s"  
    from V$ARCHIVED_LOG 
    where ((next_time-first_time)*86400<>0) and 
   first_time between to_date('2020/03/28 08:00:00','YYYY/MM/DD HH24:MI:SS')
   and to_date('2020/05/28 11:00:00','YYYY/MM/DD HH24:MI:SS') 
   and dest_id=3
   order by first_time
   

From the results, I calculate the average MB/S which is 7.67 and the maximum MB/S which is 245 MB/S

3. According to the Oracle documentation See table on recommended redo log group size

Using this query

         select * from V$LOGFILE a, V$LOG b where a.GROUP# = b.GROUP#

I discovered that I have 15 groups of 2 GB, so the redo log group size is 30 GB.

4. Oracle says that "In general we recommend adding an additional 30% on top of the peak rate", so that would mean I am expected to have 245mb/s*1.3 = 318.5 MB/S. Then here is where I get a little lost. Do I use the table in the picture I attached? If so, I would be expected to have a redo log group size of 64GB? Or am I making a connection where there should not be one?

Finally, I did also

  select optimal_logfile_size from v$instance_recovery

and that returns 14 GB.

I am having trouble making all the connections and trying to confirm, my redo log size is adequate.

Answer 1

If you have 15 groups of 2GB each, then your group size is 2GB, not 30GB.

The idea is not to switch logs too often - no more than every 20 minutes. So look at how often your log switches are happening. If you are still more than 20 minutes between switches then you are probably fine. If you are ever having more frequent switches than that, then you might need bigger logs.

Based on the calculations you performed, a max rate of ~319MB/s would indicate that individual redo log files should be 64GB, and you want a minimum (per best practice) of three redo log groups. That said - how much of your time is spent at peak load? If only a small amount of time per day (your average transaction rate is much lower) then this may be overkill. You don't want log switches to happen too far apart, either, or your ability to do point-in-time recovery after a redo log failure could be compromised.

It may make more sense for you to have log files that are 16GB and maintain a steady switch rate on average and accept a higher switch rate during peak load. You might need more individual log files that way to handle the same total transactions per minute without waiting for incomplete log switches: say three groups of 64GB each vs. 12 groups of 16GB each. The same total log capacity but in smaller chunks for switches and archive logging. That's probably why you have 15 groups of 2GB each configured now...