Database Reference
In-Depth Information
Chapter 12
Parallel Query in RAC
by Riyaj Shamsudeen
Parallel Execution (PX) is a powerful feature that makes it possible to utilize numerous processes to process millions,
if not billions, of rows. In RAC, multiple nodes can be employed to execute a single SQL statement if the data set
processed by a single SQL statement is huge.
In a single-instance database, PX server processes are allocated in the local instance. In a RAC database, PX
server processes
can
be allocated in any or all active instances depending upon the configuration. For example, a PX
query initiated in instance 1 can employ PX server processes from instances 1, 2, and 3 (in a three-node RAC cluster)
to execute a SQL statement. The session initiating a PX acts as a query co-coordinator (QC), breaks the total work into
individual units, and employs PX server processes to complete SQL statement execution. At the end of the execution,
the QC process returns the final result set or the result of SQL execution to the calling program.
Overview
In most cases, parallel statement execution employs PX servers in a classic Producer/Consumer model for SQL
execution. Producers collect and filter row pieces and distribute row pieces to the consumers, and consumers receive
the row pieces and perform further processing, such as JOIN operation. Typically, the QC process waits for the PX
servers to complete the work. It is also possible for QC to perform data processing depending upon the execution plan
chosen by the optimizer.
The Producer/Consumer model is implemented as two PX server sets. One set of PX server processes acts as
producers and the other set as consumers. For example, if you have specified a parallelism of 8, then it is probable that
your query might get 16 PX servers for that SQL statement. Eight of them will be assigned to act as a producer and the
other eight as a consumer. Essentially, a parallelism of 8 can employ 16 PX servers to execute a SQL statement.
■
There are special cases of PX execution that use just one set of PX server processes. For example, the
SQL statement
select count (*) from table
will use just one set of PX server processes. Another example is
partition-wise joins (discussed later in this chapter).
Note
The Producer/Consumer concept can be explained with a JOIN operation. Listing 12-1 prints a PX plan. The
tables PRODUCTS and SALES are joined using the HASH join technique and aggregated with a
group by
operation.
Column TQ in the execution plan specifies the PX server set.
In the following execution plan, the
PRODUCTS
table is scanned by a set of PX server
processes marked as Q1,00 in the TQ column. This PX server set acts as a producer and
distributes row pieces to the second PX server set, Q1,01.
1.
