Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 238

DOP Trace -- compute default DOP
# CPU = 16
Threads/CPU = 2 ("parallel_threads_per_cpu")
default DOP = 32 (# CPU * Threads/CPU)
default DOP = 96 (DOP * # instance)
Default DOP = 96

Although Oracle is intelligent enough to compute the DOP required for the operation, allowing the optimizer to
calculate the default every single time for every session could hinder overall performance of the environment. It's true
that with the advancement of technology, computer resources, such as CPU power, memory, and so forth are much
cheaper compared to several years ago. However, while the computer resources have become cheaper so also has
the data workload and size of the databases and the number of users increased in proportion. This means resources
are not always available in abundance; there is always a limitation. All sessions using default DOP could hinder the
performance of the database servers starving for resources. Oracle database has built-in limits and parameter settings
that can help prevent such a behavior of system overload. Parameters such as PARALLEL_MAX_SERVERS , PARALLEL_
MIN_PERCENT , PARALLEL_MIN_TIME_THRESHOLD , and so forth are examples of how the default DOP can be controlled
from chewing up available resources.

■ Note We discuss usage and definitions of these parameters later in this chapter.

At the Session Level
The DOP can also be set at the session level; this means the DOP set will apply to all queries executed within the
session. For example

ALTER SESSION FORCE PARALLEL QUERY;

ALTER SESSION ENABLE PARALLEL DML ;

Setting parallelism at the session level maybe helpful when certain functional areas of the application
require parallelism.

As a Hint in a Query
This is probably the common method of applying parallelism: using a hint as part of the statement. A hint can
be added to a query to request the optimizer to generate an execution plan that requests parallel operations. For
example, the following query has a hint to generate the default DOP:

SELECT /*+ PARALLEL */ ol_w_id,
ol_d_id,
ol_number,
SUM(ol_amount),
SUM(ol_quantity)
FROM order_line ol,
orders ord