Long Story Short
The formula for Bandwidth calculation : Required bandwidth = ((REDO rate (in bytes per second) / 0.75) * 8) / 1,000,000 (in Mbps) Two ways to calculate the REDO rate: Internet Suggested - SQL>SELECT AVG(VALUE) FROM V$SYSMETRIC_HISTORY WHERE METRIC_NAME = 'Redo Generated Per Sec';
I Suggest -
SQL>SELECT MAX(AVERAGE)
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME='Redo Generated Per Sec';
Detailed Story
Once I was looped in an email describing how to calculate the Bandwidth for DC-DR replication in a dataguard setup. Let’s go through that email first,
Dear Sir, Please do the following to calculate the Bandwidth needed for DC-DR replication:
1. Connect to production db as sysdba and run the below query:
SQL> select avg (value) from v$sysmetric_history where metric_name = 'Redo Generated Per Sec';
2. Take the value from the above script. Put the value in the below equation's Redo rate bytes per sec :
Required bandwidth = ((Redo rate bytes per sec. / 0.75) * 8) / 1,000,000 = bandwidth in Mbps
This would be your minimum bandwidth requirement for the Data Guard setup.
The person who sent this mail found this method of bandwidth calculation from a Google search. But two questions arose in my mind :
- V$SYSMETRIC_HISTORY only provides the metric history for the past hour. Will this value reflect the exact redo generation rate every time?
- Why should I divide the derived value with 0.75 ?
I found interesting answers:
- The exact moment of calculating the value of redo generation matters. For example, you should not take this value at the busiest business hour (Peak hour) of a typical business day, as unusual amount of redo may be generated during that time. Also, you can’t take this value at the weekends, as minimal redo generation rate should not be out target value. But, if you calculate the value from “V$SYSMETRIC_HISTORY” view, you will only get the value of the past hour (Max). Thus, I fear you may get a misleading value, while calculate using this view.
- We are considering a 25% loss at the internal cabling (LAN) level. From here, we got the number 0.75.
Point 2 seems “ok”. But I think we can calculate the value for “point 1” more accurately using “DBA_HIST_SYSMETRIC_SUMMARY” view, as it holds the metric data (like redo generation rate) in an hourly basis, for a week.
Let’s get some practical demonstration !
Bandwidth calculation using "V$_SYSMETRIC_HISTORY"
First we will find out the average value of the metric “redo generated per second” from “V$SYSMETRIC_HISTORY” view, for the past hour.
SQL>SELECT AVG(VALUE) FROM V$SYSMETRIC_HISTORY WHERE METRIC_NAME = 'Redo Generated Per Sec';
AVG(VALUE)
------------------
78140.5136
So, Redo rate bytes per sec = 78140.5136
Required bandwidth = ((Redo rate bytes per sec. / 0.75) * 8) / 1,000,000 in Mbps
Required bandwidth = ((78140.5136/0.75)*8)/ 1,000,000 = 0.8335 Mbps
So, based on this calculation, the bandwidth requirement is 0.8335 Mbps.
Required bandwidth = ((Redo rate bytes per sec. / 0.75) * 8) / 1,000,000 in Mbps
Required bandwidth = ((78140.5136/0.75)*8)/ 1,000,000 = 0.8335 Mbps
So, based on this calculation, the bandwidth requirement is 0.8335 Mbps.
Bandwidth calculation using "DBA_HIST_SYSMETRIC_SUMMARY"
Now we will find out two things from the view “DBA_HIST_SYSMETRIC_SUMMARY” –
- At each hour, what is the maximum value of the metric “redo generated per second”.
- At each hour, what is the average value of the metric “redo generated per second”.
SQL> select INSTANCE_NUMBER, BEGIN_TIME, END_TIME, MAXVAL, AVERAGE from dba_hist_sysmetric_summary where METRIC_NAME='Redo Generated Per Sec';
Sample Output :
Note
My suggestion is to consider the maximum value from the AVERAGE column. The maximum value of the MAXVAL column will provide an unnecessarily high value.
My suggestion is to consider the maximum value from the AVERAGE column. The maximum value of the MAXVAL column will provide an unnecessarily high value.
SQL> SELECT MAX(AVERAGE)
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME='Redo Generated Per Sec';
MAX(AVERAGE)
--------------------
6636609.29
So, Redo rate = 6636609.29 bytes per second
Required bandwidth = ((Redo rate in bytes per sec. / 0.75) * 8) / 1,000,000 in Mbps
Required bandwidth = ((6636609.29/0.75)*8)/ 1,000,000 = 70.79 Mbps = 71 Mbps Approximately.
Required bandwidth = ((Redo rate in bytes per sec. / 0.75) * 8) / 1,000,000 in Mbps
Required bandwidth = ((6636609.29/0.75)*8)/ 1,000,000 = 70.79 Mbps = 71 Mbps Approximately.
Finally ,
Huge difference between the two results!
First one using “V$SYSMETRIC_HISTORY” view gives 0.8335 Mbps while the later using “DBA_HIST_SYSMETRIC_SUMMARY” view provides 71 Mbps. You can use either solution. But the later made more sense to me. Now the choice is yours!
That’s all for today! Click here for more learning.
First one using “V$SYSMETRIC_HISTORY” view gives 0.8335 Mbps while the later using “DBA_HIST_SYSMETRIC_SUMMARY” view provides 71 Mbps. You can use either solution. But the later made more sense to me. Now the choice is yours!
That’s all for today! Click here for more learning.