Database Reference
In-Depth Information
me stress that the object-level syntax, the one that specifies the name or the alias of the object, must be used. The
following are examples of queries using that syntax to specify not only the object name (
t
for the table and
i
for the
index) but also the degree of parallelism (16):
SELECT /*+ parallel(t 16) */ * FROM t
SELECT /*+ parallel_index(t i 16) */ * FROM t
With the
parallel
hint, it's also possible to explicitly call for the default degree of parallelism:
SELECT /*+ parallel(t default) */ * FROM t
When a different degree of parallelism is specified for different tables or indexes used in a single data flow
operation, the database engine calculates a single degree of parallelism for the whole data flow operation. In general,
the degree of parallelism chosen is simply the maximum of the ones specified at the table or index level.
Automatic Degree of Parallelism
The idea behind automatic degree of parallelism is quite simple: for each SQL statement, the query optimizer
chooses the optimal degree of parallelism. Hence, the query optimizer adapts the degree of parallelism according to
the execution plan and the amount of processing that is expected to be done. As an example, Figure
15-8
shows the
degree of parallelism chosen by the query optimizer on my test server when I execute a full scan on tables of different
sizes. Note that to perform this test, I executed the
px_dop_auto.sql
script.
Figure 15-8.
Between the minimum (1) and the maximum (16), the degree of parallelism increases proportionally to
the amount of processing (the segment size in case of a full scan)
Figure
15-8
shows that, on the one hand, there is a threshold under which the query optimizer decides to run the
SQL statement serially, and on the other hand, there is a maximum degree of parallelism that isn't crossed.
The threshold is defined as the minimum amount of time that a SQL statement run serially should last (according
to the query optimizer estimations) to be considered for parallel processing. It's configured through the
parallel_
min_time_threshold
initialization parameter. The default value is
auto
, which is presently equivalent to 10 seconds.
