Testing for Scalability - Expert Oracle RAC Performance Diagnostics and Tuning - page 97

The database had the parameter “ PARALLEL_DEGREE_LIMIT ” set to the CPU; however,because this was a
datawarehouse workload, it would be appropriate to set the value of PARALLEL_DEGREE_LIMIT to I/O.
In a datawarehouse environment, where parallel operations are normal, the queries performed are I/O intensive
and not CPU intensive. Changing this parameter to I/O was optimal based on statistics collected.
Before setting the value of this parameter to I/O, it's important to understand the I/O throughput of the storage
subsystem. This can be understood using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.
CaLIBratION
i/o calibration is useful to understand the performance of the storage subsystem. oracle databases perform
different types of i/o operations depending on the type of transaction or the number of blocks requested or
persisted. it also depends on the type of workload the database will support. getting a gauge over the i/o
subsystem performance helps size and provisions the appropriate number and size of disks. the DbMs_
resoUrCe_Manager.Calibrate_io procedure calibrates the i/o subsystem using oracle code stack; this
gives a better understanding of the actual performance characteristics. Calibration is done using the following
procedure:
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (8, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
in the preceding procedure, the two important parameters are
• DISKS or NUM_PHYSICAL_DISKS : like the parameter clearly states, it's the physical disks not the
number of lUn devices.
• MAX_LATENCY : this is the maximum latency for the disk access specified in milliseconds.
once calibration is complete, the results of the calibration can be checked using the following script:
Script: MVRACPDnTap_iocalcheck1.sql
Last Calibration Max Max Max
Date IOPS MBPS PMBPS Latency Phy Disks
--------------- -------- -------- -------- -------- ---------
10-FEB-13 11.50 20738 1613 747 11 8