Measuring the volume of Oracle redo size Oracle Consulting Tips by Burleson

The redo size Oracle metric is the total amount of redo generated in bytes.

Mark Rittman notes on redo log sizing says that increasing REDO Log size and log_buffer parameter will reduce log switch waits and improve overall time for OLAP operations.

This complex query will show redo size by time period:

col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT  sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time ,  startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY  startup_time, sysst.instance_number
                ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
EXTRACT (DAY    FROM (end_interval_time-begin_interval_time))*24*60*60+
            EXTRACT (HOUR   FROM (end_interval_time-begin_interval_time))*60*60+
            EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+
            EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA
  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value) redo1
from redo_sz
group by  instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2;

Rampant author Ben Prusinski (author of Exploring Oracle Internals), offers these tips for measuring the amount of redo log generation:

One topic for new projects such as 11g Data Guard and 11g Streams is a way to calculate the total volume of redo generated. I am going to show you a quick and dirty way to calculate the amount of redo generated by an Oracle 11g database.

Now lets examine an 11g AWR report:

WORKLOAD REPOSITORY report for . . .

Load Profile Per Second      Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
 . . .
Redo size:   62.9            5,874.9

So in our example above, we had a grand total of 62.9K of redo per second.

We can correlate redo log size to the V$ data dictionary performance views with the following SQL*PLUS script that queries against the v$log and v$database views:

select
   Start_Date,
   Start_Time,
   Num_Logs,
   Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
   Vdb.NAME AS Dbname
FROM
   (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time,
   COUNT(Vlh.Thread#) Num_Logs
FROM
   V$log_History Vlh
GROUP BY
   To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
   To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
   V$log Vl,
   V$database Vdb
WHERE
   Vl.Group# = 1
ORDER BY
   Log_Hist.Start_Date,
   Log_Hist.Start_Time;

START_DATE START NUM_LOGS MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2009-03-21 15:00 8 400 BEN11G
2009-03-21 16:00 1 50 BEN11G
2009-03-22 10:00 1 50 BEN11G
2009-03-23 17:00 4 200 BEN11G
2009-03-25 21:00 2 100 BEN11G
2009-03-26 13:00 2 100 BEN11G
2009-04-06 22:00 1 50 BEN11G

We can then take the number of redo logs from the NUM_LOGS column of the above query and multiply by size of each online redo log (ORL) file.

select
   group#,
   members,
   bytes,
   archived,
   status
from
   v$log;

GROUP#      MEMBERS   BYTES      ARC STATUS
---------- ---------- ---------- --- ----------------
1          1          52428800   YES INACTIVE
2          1          52428800   NO  CURRENT
3          1          52428800   YES INACTIVE

Another method to calculate redo generated for Oracle is to examine the pattern of log switches that occur within your Oracle database. Upon examination of your alert.log file, you can take a differential of log switches to assess exactly how much redo is generated at peak and idle times.

First lets perform some log switches in our test 11g database!

SQL> alter system switch logfile;

System altered.

Note: we are using 11gR1 on Oracle Enterprise Linux 5.2 for the examples here. So we start a new ADRCI session

[oracle@sandiego ~]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Fri Aug 7 18:41:22 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/rdbms/ben11g/ben11g
2: diag/clients/user_oracle/host_3399978961_11
3: diag/clients/user_unknown/host_411310321_11
4: diag/tnslsnr/sandiego/listener
Q: to quit


2009-08-07 18:45:58.653000 -07:00
Thread 1 advanced to log sequence 21
Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:03.171000 -07:00
Thread 1 advanced to log sequence 22
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
2009-08-07 18:46:05.556000 -07:00
Thread 1 advanced to log sequence 23
Current log# 2 seq# 23 mem# 0: /u01/app/oracle/oradata/ben11g/redo02.log
2009-08-07 18:46:07.060000 -07:00
Thread 1 advanced to log sequence 24
Current log# 3 seq# 24 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:09.209000 -07:00
Thread 1 advanced to log sequence 25
Current log# 1 seq# 25 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log

You can add up the total number of logs between log switches and divide by total time to obtain a rough estimate of the number of bytes of redo log generation.

By understanding how to calculate redo generation, you will be prepared to understand total redo log size and how much network bandwidth you will require to efficiently move the redo logs between source and target databases.