Parallel Execution

Parallel execution divides the task of executing a SQL statement into multiple small
units, each of which is executed by a separate process. Also the incoming data (tables,
indexes, partitions) can be divided into parts called granules. The user shadow process
that wants to execute a query in parallel takes on the role as parallel execution
coordinator or query coordinator. The query coordinator does the following:

■ Parses the query and determines the degree of parallelism
■ Allocates one or two set of slaves (threads or processes)
■ Controls the query and sends instructions to the PQ slaves
■ Determines which tables or indexes need to be scanned by the PQ slaves
■ Produces the final output to the user



Degree of Parallelism
The parallel execution coordinator may enlist two or more of the instance's parallel
execution servers to process a SQL statement. The number of parallel execution
servers associated with a single operation is known as the degree of parallelism.
A single operation is a part of a SQL statement such as an order by, a full table scan to perform a join on a nonindexed column table.

Note that the degree of parallelism applies directly only to intra-operation parallelism. If inter-operation parallelism is possible, the total number of parallel execution servers for a statement can be twice the specified degree of parallelism. No more than two sets of parallel execution servers can run simultaneously. Each set of parallel execution servers may process multiple operations. Only two sets of parallel execution servers need to be active to guarantee optimal inter-operation parallelism.

Parallel execution is designed to effectively use multiple CPUs and disks to answer
queries quickly. When multiple users use parallel execution at the same time, it is easy to quickly exhaust available CPU, memory, and disk resources.
Oracle Database provides several ways to manage resource utilization in conjunction
with parallel execution environments, including:

■ The adaptive multiuser algorithm, which is enabled by default, reduces the degree
of parallelism as the load on the system increases.
■ User resource limits and profiles, which allow you to set limits on the amount of
various system resources available to each user as part of a user's security domain.
■ The Database Resource Manager, which lets you allocate resources to different
groups of users.


The Parallel Execution Server Pool
When an instance starts up, Oracle creates a pool of parallel execution servers
which are available for any parallel operation. The initialization parameter
PARALLEL_MIN_SERVERS specifies the number of parallel execution servers that
Oracle Database creates at instance startup.

When executing a parallel operation, the parallel execution coordinator obtains
parallel execution servers from the pool and assigns them to the operation. If
necessary, Oracle can create additional parallel execution servers for the operation.
These parallel execution servers remain with the operation throughout job execution,
then become available for other operations. After the statement has been processed
completely, the parallel execution servers return to the pool.

Note that the parallel execution coordinator and the parallel execution servers can
only service one statement at a time. A parallel execution coordinator cannot
coordinate, for example, a parallel query and a parallel DML statement at the same
time.

When a user issues a SQL statement, the optimizer decides whether to execute the
operations in parallel and determines the degree of parallelism (DOP) for each
operation. You can specify the number of parallel execution servers required for an
operation in various ways.

If the optimizer targets the statement for parallel processing, the following sequence of events takes place:

1. The SQL statement's foreground process becomes a parallel execution coordinator.
2. The parallel execution coordinator obtains as many parallel execution servers as
needed (determined by the DOP) from the server pool or creates new parallel
execution servers as needed.
3. Oracle executes the statement as a sequence of operations. Each operation is
performed in parallel, if possible.
4. When statement processing is completed, the coordinator returns any resulting
data to the user process that issued the statement and returns the parallel
execution servers to the server pool.

The parallel execution coordinator calls upon the parallel execution servers during the execution of the SQL statement, not during the parsing of the statement. Therefore, when parallel execution is used with the shared server, the server process that processes the EXECUTE call of a user's statement becomes the parallel execution
coordinator for the statement.

Posted in: Parallel Execution