Database Reference
In-Depth Information
The maximum degree of parallelism depends on the
parallel_degree_limit
initialization parameter. It can be
set to one of the following values:
CPU
: The maximum degree of parallelism is equal to the default degree of parallelism. This is
the default value.
•
IO
: The maximum degree of parallelism is defined by disk I/O cap. Refer to the “Disk I/O Cap”
section later in this chapter for additional information about it.
•
•
An integer value explicitly specifies the maximum degree of parallelism.
To use automatic degree of parallelism, two conditions have to be fulfilled. First, statistics gathered through
I/O calibration have to be available. What these statistics are and how to gather them is covered in the next section,
specifically in the “Disk I/O Cap” subsection. Second, the feature has to be enabled either through the
parallel_
degree_policy
initialization parameter or the
parallel(auto)
hint. When the
parallel_degree_policy
initialization
parameter is set to
limited
, there is an additional requirement: only tables and indexes having an associated default
degree of parallelism are considered for parallel processing. The following example, based on the output of the
px_dop_limited.sql
script, illustrates this:
SQL> ALTER SESSION SET parallel_degree_policy = limited;
SQL> ALTER TABLE t
NOPARALLEL
;
SQL> SELECT * FROM t;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
SQL> ALTER TABLE t
PARALLEL
;
SQL> SELECT * FROM t;
----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | Q1,00 | PCWP | |
----------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
