Database Reference
In-Depth Information
auto
: Automatic degree of parallelism is enabled for all SQL statements. In addition, two other
features are enabled:
parallel statement queuing
and
in-memory parallel execution
.
•
adaptive
: This mode is similar to
auto
. The only difference is that
performance feedback
is
also enabled. This value is available as of version 12.1 only.
•
■
parallel statement queuing, in-memory parallel execution, and performance feedback are unrelated to
automatic degree of parallelism. It's therefore unfortunate that they're all activated with a single initialization parameter.
It would be much better to be able to activate them selectively.
Note
The
parallel_degree_policy
initialization parameter can be set at the session level and, as of version 12.1, at
the PDB level. It's also possible to override its value at the SQL statement level by specifying the
parallel
hint with
the statement-level syntax. The
parallel
hint supports the following values:
parallel(manual)
activates manual degree of parallelism.
•
parallel(auto)
activates automatic degree of parallelism (but it doesn't activate parallel
statement queuing and in-memory parallel execution).
•
parallel(
n
)
sets the degree of parallelism to the integer value specified as parameter (
n
).
•
■
From version 11.2 onward, the
parallel
hint supports two syntaxes: statement-level and object-level. The
statement-level syntax, as just described, overrides the
parallel_degree_policy
initialization parameter at the SQL
statement level. The object-level syntax, covered in the upcoming “Manual degree of parallelism” section, overrides the
degree of parallelism associated to each table and index.
Caution
The aim of the following sections is to describe how the database engine determines the degree of parallelism
without considering that the number of slave processes might be capped by the load on the system or other factors.
Later on, the “Limiting the Degree of Parallelism” section describes situations in which the degree of parallelism could
be reduced.
Default Degree of Parallelism
What is commonly called the
default degree of parallelism
is actually the maximum degree of parallelism you might
want to use for any parallel SQL statement. In fact, the default value is only good if you want to run at most one SQL
statement in parallel at any given time. How and when this default value is used depends on several factors, like
the database instance configuration. The next two sections, while discussing how manual and automatic degree of
parallelism work, provide more information about the default degree of parallelism.
To compute the default degree of parallelism, as shown by Formula 15-2, the database engine multiplies the
value of the
cpu_count
initialization parameters by the number of slave processes that a CPU core is expected to
handle (the
parallel_threads_per_cpu
initialization parameter). In the case of a RAC, the resulting value is further
multiplied by the number of database instances in the cluster.
