Database Reference
In-Depth Information
The last line of the preceding example shows that the note section generated by the
dbms_xplan
package clearly
states whether automatic degree of parallelism is used. And, if it's used, the chosen degree of parallelism is mentioned.
Other messages related to automatic degree of parallelism that can show up in the note section are the following:
automatic DOP: Computed Degree of Parallelism is 2
automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
automatic DOP: skipped because of IO calibrate statistics are missing
If the degree of parallelism selected by the query optimizer is suboptimal, from version 12.1 onward, you can adjust
the computation through the
parallel_degree_level
initialization parameter. Its default value is 100. If you specify
values lower than 100, the degree of parallelism decreases proportionally. For example, with the value 50, the degree of
parallelism reduces by 50%. If you specify values higher than 100, the degree of parallelism increases proportionally. For
example, with the value 200 the degree of parallelism, provided the maximum isn't crossed, doubles.
Limiting the Degree of Parallelism
The previous section describes how the database engine determines the degree of parallelism; this section describes
those situations in which the degree of parallelism determined by the database engine can be reduced. Specifically, a
reduction takes place when:
•
adaptive parallelism is enabled,
•
disk I/O cap is enabled,
•
the Database Resource Manager caps the degree of parallelism, and
•
a user profile limits the number of concurrent sessions a specific user can have.
Note that several of these features can also be concurrently enabled.
■
The query optimizer is aware of the limitations imposed by the techniques described in this section only
when two conditions are fulfilled: the cap is imposed by the database resource Manager, and automatic degree of
parallelism is used. In all other situations, the query optimizer is unaware of the fact that a limitation to the degree of
parallelism is in place. note that being aware of this is essential because the costs estimated by the query optimizer do
depend on the degree of parallelism. as a result, when the limitation isn't known, the query optimizer might choose a
suboptimal execution plan.
Caution
Adaptive Parallelism
Adaptive parallelism is controlled by the
parallel_adaptive_multi_user
initialization parameter. Its purpose is to
influence the number of slave processes assigned to a server process. It accepts two values:
FALSE
: If the pool isn't exhausted, the requested number of slave processes is assigned to the
server process.
•
TRUE
: As the number of already assigned slave processes increases, the requested degree of
•
