Database Reference
In-Depth Information
Formula 15-2.
The default degree of parallelism is the maximum degree of parallelism you might want to use for any
parallel SQL statement
default_dop pu_count parallel_threads_per_cpunumber_of_i
=
×
×
nstances
■
on most platforms, the default value of the
parallel_threads_per_cpu
initialization parameter is 2. In case
multithreading is enabled at the CpU level and, as a result, the value of the
cpu_count
initialization parameters is
artificially inflated, I advise you to set the
parallel_threads_per_cpu
initialization parameter to 1.
Tip
Manual Degree of Parallelism
A degree of parallelism is associated to each table and index. It's used by default for the operations referencing it. Its
default value is 1, which means that no parallel processing is used. As shown in the following SQL statements, the
degree of parallelism is set with the
PARALLEL
clause, either when an object is created or later:
CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
PARALLEL 4
ALTER TABLE t
PARALLEL 2
CREATE INDEX i ON t (id)
PARALLEL 4
ALTER INDEX i
PARALLEL 2
■
It's quite common to use parallel processing to improve the performance of maintenance tasks or batch
jobs that create tables or indexes. For that purpose, it's common to specify the
PARALLEL
clause. Be aware, though, that
when this clause is used, the degree of parallelism is used not only during the creation of the table or index but also later
for the operations executed on it. Therefore, if you want to use parallel processing only during the creation of a table or
index, it's essential that you alter the degree of parallelism once created.
Caution
To disable parallel processing, either the degree of parallelism is set to 1 or the
NOPARALLEL
clause is specified:
ALTER TABLE t PARALLEL 1
ALTER INDEX i NOPARALLEL
When the
PARALLEL
clause is used without specifying a degree of parallelism (for example,
ALTER TABLE t
PARALLEL
), the default degree of parallelism is used. Because the default value is only good if you want to run at most
one SQL statement in parallel at any given time, I usually recommend specifying a value.
To override the degree of parallelism defined at the table and index levels, it's possible to use the
parallel
,
no_parallel
,
parallel_index
, and
no_parallel_index
hints. In fact, when these hints are used with the object-level
syntax, the first two override the setting at the table level, and the third and fourth override it at the index level. Let
