Databases Reference
In-Depth Information
In the example in Listing 5-9, we will assume that we will have four additional databases, each with 10% the IOPs
demand of our original database, and add a 30% margin of error for our IOPs calculations:
Listing 5-9.
lst05-09-awr-iops-sum-extr.sql
SYS @ PROD> select
avg(IOPsr) AvgIOPsr, max(IOPsr) MaxIOPsr,
avg(IOPsw) AvgIOPsw, max(IOPsw) MaxIOPsw,
avg(IOPsr) AvgIOPsredo, max(IOPsredo) MaxIOPsredo,
avg(Totiops) AvgTotiops, max(Totiops) MaxTotiops
from (
select
(IOPsr + (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * IOPsr)) IOPsr,
(IOPsw + (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * IOPsw)) IOPsw,
(IOPsredo + (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * IOPsredo)) IOPsredo,
(Totiops+ (&&num_addtl_dbs * &&pct_resource_load * &&margin_err * Totiops)) Totiops from (
{ ... Code from Listing 5-7 ... }
)
/
Enter value for num_addtl_dbs: 4
Enter value for pct_resource_load: .1
Enter value for margin_err: 1.3
Avg IOPs| Max IOPs| Avg IOPs| Max IOPs| Avg IOPs| Max IOPs| Avg IOPs| Max IOPs
Reads| Reads| Write| Write| Redo| Redo| Total| Total
---------|----------|----------|----------|----------|----------|----------|----------
656.73| 3080.95| 96.64| 1525.04| 656.73| 443.75| 832.14| 3438.89
With estimated forecasts for additional environments and their relative IOPs weight, our adjusted IOPs demand
is now 3438.89.
Finally, take the information collected thus far and map these requirements to disk IOPs capabilities on Exadata.
The following script evaluates IOPs data from AWR and compares with Exadata IOPs capabilities for both High
Performance and High Capacity SAS disks, taking into consideration whether you will use Oracle ASM normal or high
redundancy (see Recipe 5-4 for additional details). In Listing 5-10, two additional input parameters are provided:
&asm_redundancy_data
is used to multiply the calculated write IOPs by either two or three,
depending on whether normal or high redundancy is used for database files
•
&asm_redundancy_reco
is used to multiply the calculated redo IOPs by either two or three,
depending on whether normal or high redundancy is used for redo log files
•
■
Note
please refer to recipe 1-1 to learn more about Exadata IOps capabilities.
Listing 5-10.
lst05-10-awr-iops-forexa.sql
SYS @ PROD> select
max(IOPsr) MaxIOPsr,
max(IOPsw) MaxIOPsw,
Search WWH ::
Custom Search