Sizing Exadata - Oracle Exadata Recipes: A Problem-Solution Approach - page 112

avg(Totmbps) AvgTotmbps, max(Totmbps) MaxTotmbps
from ( { ... Code from Listing 5-11 ... } )
/
Avg MBPS|Max MBPS|Avg MBPS|Max MBPS|Avg MBPS|Max MBPS|Avg MBPS| Max MBPS
Reads| Reads| Write| Write| Redo| Redo| Total| Total
--------|--------|--------|--------|--------|--------|--------|----------
32.47| 209.66| 1.89| 11.68| 0.33| 3.63| 34.69| 218.18

Based on the numbers above you can see that the maximum I/O bandwidth in MB/second from the AWR sample
set is approximately 218 MB/second.
Next, introduce logic into your script to extrapolate your I/O demand numbers to account for additional
databases, an estimated percentage of I/O bandwidth demand expected across these databases, as well as add a
“margin of error.” In the next script:

•

&num_addtl_dbs = Number of additional, potentially non-production databases that we will
plan on consolidating on the same Exadata Database Machine.

•

&pct_resource_load = Estimated fraction of I/O bandwidth demand that each additional
environment will consume with respect to the database analyzed. For this, “1” = equal IOPS
demand compared to the database you're running the analysis on, “.1” = 10% the IOPS
demand of your database, and so forth.

•

&margin_err = Factor of safety. For this, “1” = no margin of error, “2” = multiply our measured
IOPs numbers by two, and so on.

In the example in Listing 5-13, we will assume five additional databases, each with a 1:2 ratio of I/O demand
compared to our current database (1:2 = 50% = pct_resource_load=.5), and a 25% margin of error:

Listing 5-13. lst05-13-awr-iobw-sum-extr.sql

SYS @ PROD> select avg(IOmbpsr) AvgIOmbpsr, max(IOmbpsr) MaxIOMbpsr,
avg(IOmbpsw) AvgIOmbpsw, max(IOmbpsw) MaxIOMbpsw,
avg(IOmbpsredo) AvgIOmbpsredo, max(IOmbpsredo) MaxIOMbpsredo,
avg(Totmbps) AvgTotmbps, max(Totmbps) MaxTotmbps
from (
select (IOmbpsr + (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * IOmbpsr)) IOmbpsr,
(IOmbpsw + (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * IOmbpsw)) IOmbpsw,
(IOmbpsredo + (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * IOmbpsredo))
IOmbpsredo,
(Totmbps+ (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * Totmbps)) Totmbps
from (
{ ... Code from Listing 5-11 ...}
/
Enter value for num_addtl_dbs: 5
Enter value for pct_resource_load: .5
Enter value for margin_err: 1.25

Avg MBPS| Max MBPS| Avg MBPS| Max MBPS| Avg MBPS| Max MBPS| Avg MBPS| Max MBPS
Reads| Reads| Write| Write| Redo| Redo| Total| Total
---------|----------|----------|----------|----------|----------|----------|----------
133.95| 864.85| 7.78| 48.19| 1.37| 14.99| 143.10| 900.00

With these revised, extrapolated numbers, our I/O bandwidth demand maximum values are now approximately
900 MB/second.