Database Reference
In-Depth Information
Disk I/O Cap
Disk I/O cap is a feature available from version 11.1 onward. Its purpose is to cap the default degree of parallelism
according to the maximum throughput that the disk I/O subsystem can sustain. Let me stress, it only caps the default
degree of parallelism. As a result, if the default degree of parallelism isn't relevant (for example, when using the
manual degree of parallelism by specifying a particular value), disk I/O cap has no impact at all.
Disk I/O cap is especially useful for those systems that are I/O bound because of an unbalanced configuration. In
the case of parallel processing, an unbalanced configuration often means too high a number of CPU cores compared
to the throughput that the disk I/O subsystem can sustain.
■
a good rule of thumb for dimensioning a database server that's intended to support a large number of parallel
SQL statements (for example, a typical database server used for a data warehouse), is that the throughput the disk I/o
subsystem should be able to sustain is as high as the number of CpU cores multiplied by 200 MB/s. For example, if a
database server has 16 CpU cores, its disk I/o subsystem should sustain 3,200 MB/s.
Tip
To use disk I/O cap, two conditions have to be fulfilled. First, the feature has to be enabled through an
initialization parameter. Which one depends on the version you're using:
parallel_io_cap_enabled
initialization parameter has to be set to
TRUE
(the default value is
FALSE
).
•
In version 11.1, the
parallel_degree_limit
initialization parameter has to be set
to
IO
(the default value is
CPU
). Note that, as of version 11.2, the
parallel_io_cap_enabled
initialization parameter should be avoided because it's deprecated.
•
From version 11.2 onward, the
Second, statistics gathered through I/O calibration have to be available. These statistics are required because they
provide the database engine with information about the maximum throughput the disk I/O subsystem can sustain. To
gather them, the
calibrate_io
procedure of the
dbms_resource_manager
package has to be executed. The following
PL/SQL block, an excerpt of the
px_calibrate_io.sql
script, shows how to do it. Notice that the
num_physical_disks
parameter has to be set to the number of physical disks on which the database is stored (in my test system, I have ten
disks provisioned through ASM).
DECLARE
l_max_iops PLS_INTEGER;
l_max_mbps PLS_INTEGER;
l_actual_latency PLS_INTEGER;
BEGIN
dbms_resource_manager.calibrate_io(
num_physical_disks => 10,
max_iops => l_max_iops,
max_mbps => l_max_mbps,
actual_latency => l_actual_latency
);
END;
