Database Reference
In-Depth Information
When to Use It
Parallel processing should be used only when two conditions are met. First, you can use it when plenty of free
resources (CPU, memory, and disk I/O bandwidth) are available. Remember, the aim of parallel processing is to
reduce the response time by distributing the work usually done by a single process (and hence a single CPU core) to
several processes (and hence several CPU cores). Second, you can use it for SQL statements that take more than a
dozen seconds to execute serially; otherwise, the time and resources needed to initialize, coordinate and terminate
the parallel environment (mainly, the slave processes and the table queues) might be higher than the time gained by
the parallelization itself. The actual limit depends on the amount of resources that are available. Therefore, in some
situations, only SQL statements that take more than a few minutes, or even longer, are good candidates for being
executed in parallel. It's important to stress that if these two conditions aren't met, performance could decrease
instead of increase.
If parallel processing is commonly used for many SQL statements, either automatic degree of parallelism is
enabled at the system level or manual degree of parallelism is enabled at the segment levels. Otherwise, if it's used
only for specific batches or reports, it's usually better to enable it at the session level or through hints.
Pitfalls and Fallacies
It's very important to understand that the
parallel
and
parallel_index
hints using the object-level syntax don't
force the query optimizer to use parallel processing. Instead, they override the degree of parallelism defined at the
table or index level. This change, in turn, allows the query optimizer to consider parallel processing with the specified
degree of parallelism. This means that the query optimizer considers execution plans with and without parallel
processing and, as usual, picks out the one with the lower cost. Let me stress this point by showing you an example
based on the
px_dop_manual.sql
script. As shown in the following SQL statements, the cost associated with a full
table scan decreases proportionally to the degree of parallelism (refer to Chapter 7 for addition information about the
cost of parallel operations):
SQL> EXPLAIN PLAN SET STATEMENT_ID 'dop1' FOR
2 SELECT /*+ full(t) parallel(t 1) */ * FROM t WHERE id > 93000;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'dop2' FOR
2 SELECT /*+ full(t) parallel(t 2) */ * FROM t WHERE id > 93000;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'dop3' FOR
2 SELECT /*+ full(t) parallel(t 3) */ * FROM t WHERE id > 93000;
SQL> EXPLAIN PLAN SET STATEMENT_ID 'dop4' FOR
2 SELECT /*+ full(t) parallel(t 4) */ * FROM t WHERE id > 93000;
SQL> SELECT statement_id, cost
2 FROM plan_table
3 WHERE id = 0;
STATEMENT_ID COST
------------ ----
dop1 296
dop2 164
dop3 110
dop4 82
