Database Reference
In-Depth Information
The gathering of statistics takes a few minutes. Once that work is over, the resulting statistics are externalized
through the
dba_rsrc_io_calibrate
view. There are two values that are relevant for disk I/O cap: the maximum
throughput that the disk I/O subsystem can sustain (
max_mbps
) and the maximum throughput that a single server
process can sustain (
max_pmbps
). On my test systems they're as follows:
SQL> SELECT max_mbps, max_pmbps
2 FROM dba_rsrc_io_calibrate;
MAX_MBPS MAX_PMBPS
-------- ---------
664 297
Based on the two values returned by the preceding query, the database engine computes the maximum degree
of parallelism, as shown by Formula 15-3. If the resulting value is lower than the default degree of parallelism, it
becomes the new default. If the resulting value is higher than the default degree of parallelism, it's ignored.
Formula 15-3.
The default degree of parallelism is capped by the ratio between the maximum throughput the disk
I/O subsystem can sustain and the maximum throughput a single server process can sustain
max_mbps
max_pmbps
max_default_dop
=
Database Resource Manager
The Resource Manager provides control of database resources allocated to server processes. Among other things,
it can be used to cap the degree of parallelism to a specific value. Because describing the Resource Manager in
detail goes beyond the scope of this chapter (refer to the
Oracle Database Administrator's Guide
manual for more
information), I'll just provide one example based on the
px_rm_cap_dop.sql
script. This example shows how to
configure the Resource Manager to cap the degree of parallelism of the SQL statements executed by a specific user to
eight. The configuration steps are the following:
Create a resource plan called
control_dop
that, through the
cap_dop
consumer group,
limits the degree of parallelism to eight:
1.
BEGIN
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan(
plan => 'CONTROL_DOP',
comment => 'Control the degree of parallelism'
);
dbms_resource_manager.create_consumer_group (
consumer_group => 'CAP_DOP',
comment => 'Users with a restricted degree of parallelism'
);
dbms_resource_manager.create_plan_directive(
plan => 'CONTROL_DOP',
group_or_subplan => 'CAP_DOP',
comment => 'Cap degree of parallelism',
parallel_degree_limit_p1
=>
8
);
