For example, if a table has ten partitions and a parallel operation divides the work evenly among them, you can use ten parallel server processes (degree of
parallelism = 10) to do the work in approximately one-tenth the time that one
Parallel Execution 21-15
process would take, or you can use five processes to do the work in one-fifth the time, or two processes to do the work in one-half the time.
If, however, you use nine processes to work on ten partitions, the first process to finish its work on one partition then begins work on the tenth partition; and as the other processes finish their work they become idle. This does not give
good performance when the work is evenly divided among partitions. When
the work is unevenly divided, the performance varies depending on whether
the partition that is left for last has more or less work than the other partitions.
Similarly, if you use four processes to work on ten partitions and the work is evenly divided, then each process works on a second partition after finishing
its first partition, but only two processes work on a third partition while the other two remain idle.
In general, you cannot assume that the time taken to perform a parallel
operation on N partitions with P parallel server processes will be N/P,
because of the possibility that some processes might have to wait while others finish working on the last partition(s). By choosing an appropriate degree of
parallelism, however, you can minimize the workload skewing and optimize
performance.
For information about balancing the work load with disk affinity, see “Affinity
and Parallel DML” on page 21-40.
Parallelization Rules for SQL Statements
A SQL statement can be parallelized if it includes a parallel hint or if the table or index being operated on has been declared PARALLEL with a CREATE or
ALTER statement. In addition, a data definition language (DDL) statement can
be parallelized by using the PARALLEL clause. However, not all of these
methods apply to all types of SQL statements.
Parallelization has two components: the decision to parallelize and the degree of parallelism. These components are determined differently for queries, DDL
operations, and DML operations.
To determine the degree of parallelism, Oracle looks at the reference objects.
•
Parallel query looks at each table and index, in the portion of the query
being parallelized, to determine which is the reference table. The basic
rule is to pick the table or index with the largest degree of parallelism.
•
For parallel DML (insert, update, and delete), the reference object that
determines the degree of parallelism is the table being modified by an
insert, update, or delete operation. Parallel DML also adds some limits
21-16 Oracle8 Server Concepts
to the degree of parallelism to prevent deadlock. If the parallel DML
statement includes a subquery, the subquery’s degree of parallelism is
the same as the DML operation.
•
For parallel DDL, the reference object that determines the degree of
parallelism is the table, index, or partition being created, rebuilt, split, or moved. If the parallel DDL statement includes a subquery, the
subquery’s degree of parallelism is the same as the DDL operation.
Rules for Parallelizing Queries
Decision to Parallelize
A SELECT statement can be parallelized only if the following conditions are
satisfied:
1.
The query includes a “parallel” hint specification (PARALLEL or
PARALLEL_INDEX) or the schema objects referred to in the query have
a PARALLEL declaration associated with them.
2.
At least one of the tables specified in the query requires one of the
following:
•
a full table scan
•
an index range scan spanning multiple partitions
Degree of Parallelism
The degree of parallelism for a query is determined by the following rules:
1.
The query uses the maximum degree of parallelism taken from all of the
table declarations involved in the query and all of the potential indexes
that are candidates to satisfy the query (the reference objects). That is, the table or index that has the greatest degree of parallelism determines the
query’s degree of parallelism ( maximum query directive).
2.
If a table has both a “parallel” hint specification in the query and a
parallel declaration in its table specification, the hint specification takes
precedence over parallel declaration specification.
Rules for Parallelizing UPDATE and DELETE
Update and delete operations are parallelized by partition. Updates and
deletes can only be parallelized on partitioned tables; update/delete
parallelism is not possible within a partition, nor on a nonpartitioned table.
You have two ways to specify parallel directives for UPDATE and DELETE
operations (assuming that PARALLEL DML mode is enabled):
1.
Parallel clause specified in the definition of the table being updated
Parallel Execution 21-17
or deleted (the reference object).