Parallelizing SQL Statements

Each SQL statement undergoes an optimization and parallelization process when it is
parsed. When the data changes, if a more optimal execution or parallelization plan
becomes available, Oracle can automatically adapt to the new situation.
After the optimizer determines the execution plan of a statement, the parallel
execution coordinator determines the parallelization method for each operation in the
plan.

For example, the parallelization method might be to parallelize a full table scan
by block range or parallelize an index range scan by partition. The coordinator must
decide whether an operation can be performed in parallel and, if so, how many
parallel execution servers to enlist. The number of parallel execution servers in one set is the DOP.

Dividing Work Among Parallel Execution Servers
The parallel execution coordinator examines the redistribution requirements of each
operation. An operation's redistribution requirement is the way in which the rows
operated on by the operation must be divided or redistributed among the parallel
execution servers.

After determining the redistribution requirement for each operation in the execution
plan, the optimizer determines the order in which the operations must be performed.
With this information, the optimizer determines the data flow of the statement.
As an example of parallel query with intra- and inter-operation parallelism, consider
the following, more complex query:

SELECT /*+ PARALLEL(employees 4) PARALLEL(departments 4)
USE_HASH(employees) ORDERED */ MAX(salary), AVG(salary)
FROM employees, departments
WHERE employees.department_id = departments.department_id
GROUP BY employees.department_id;

Note that hints have been used in the query to force the join order and join method,
and to specify the DOP of the tables employees and departments. In general, you
should let the optimizer determine the order and method.

Posted in: Parallel Execution