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.
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
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
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.
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.
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.
No comments:
Post a Comment