DBA Quick Tip of the week: Measuring Redo Generated

One topic that DBAs frequently need to assess when planning for new projects such as 11g Data Guard and 11g Streams is how to calculate redo generated by an Oracle database. This is key to understand because redo generation affects throughput and performance with both standby databases and replication based activities. I am going to show you a quick and dirty way to calculate the amount of redo generated by an Oracle 11g database. To do so, you will need to run a new 11g Automatic Workload Repository (AWR) report either from Oracle Enterprise Manager or via the awrrpt.sql script.
Second, your database must currently be licensed to use the AWR


Now lets examine our 11g AWR report:

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
BEN11G 3769640271 ben11g 1 18-Jun-09 15:17 11.1.0.6.0 NO

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
sandiego.localdo Linux IA (32-bit) 1 .99

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 24 04-Aug-09 12:55:42 37 1.2
End Snap: 30 07-Aug-09 18:11:18 36 1.0
Elapsed: 4,635.59 (mins)
DB Time: 1.47 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 76M 76M Std Block Size: 8K
Shared Pool Size: 156M 156M Log Buffer: 5,988K


Scroll down to the following section of the AWR report:

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.0 0.0 0.00 0.03
DB CPU(s): 0.0 0.0 0.00 0.03
Redo size: 62.9 5,874.9
Logical reads: 1.2 113.1
Block changes: 0.3 31.5
Physical reads: 0.0 2.0
Physical writes: 0.0 2.0
User calls: 0.0 1.0
Parses: 0.1 9.9
Hard parses: 0.0 0.1
W/A MB processed: 8,664.4 808,959.0
Logons: 0.0 0.3
Executes: 0.2 18.8
Rollbacks: 0.0 0.0
Transactions: 0.0


Under the heading for Load Profile generated by the AWR, you can examine redo size generated by second, per transaction, per execution or per system call. So in our example above, we had a grand total of 62.9K of redo per second. We can correlate this to the V$ data dictionary performance views with the following SQL*PLUS script that queries against the V$LOG and V$DATABASE performance 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.


SQL> select group#, members, bytes, archived, status
2 from v$log
3 ;

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


I will leave the math calculations up to you.

For those of you who cannot afford the AWR licensed option for 10g/11g, in this down economy, I will provide you with a low cost low tech way to figure out how much redo is generated by Oracle.

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



So you can add up the total number of logs between log switches and divide by total time to obtain a rough estimate.

By understanding how to calculate redo generation, you will be prepared to understand one key component of planning for your Data Guard, Streams or replication environments as this will give you some idea on how much network bandwidth you will require to efficiently move the redo logs between source and target databases.



UPDATE:


Fellow Oracle professional, Coskan Gundogar provided a SQL script to examine redo log generation. Here is the sample output:

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 9 17:06:48 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select trunc(first_time),sum(blocks*block_size)
from (select distinct first_change#,first_time,blocks,block_size,completion_time
from v$archived_log)
group by trunc(first_time)
order by trunc(first_time);

TRUNC(FIR SUM(BLOCKS*BLOCK_SIZE)
--------- ----------------------
26-MAR-09 51228160
06-APR-09 44119040
18-JUN-09 35488768
07-AUG-09 51233280

The primary difference in this script is that it queries redo generation from the V$ARCHIVED_LOG dynamic performance view rather than the earlier script that queries the V$LOG_HISTORY and V$LOG dynamic performance views.

So there you have it, multiple ways to calculate exactly how much redo is generated by your Oracle database! Stay tuned, next time we will provide some quick tips on tuning network and disk I/O for your Oracle database.

Cheers,
Ben