Oracle Parallel Processing

Using EXPLAIN PLAN to View Parallel Execution

The sequence of operations that Oracle performs to execute a SQL statement is called an execution plan. Sometimes it’s useful to look at the execution plan for a statement, in order to determine whether the plan picked by the optimizer is an efficient one. You can use Oracle’s EXPLAIN PLAN statement for this purpose. EXPLAIN PLAN takes a SQL statement, determines the execution plan, and stores that plan in a special table known as the plan table.

Tip

If you are not familiar with the use of EXPLAIN PLAN, you may want to consult Oracle Corporation’s Oracle8 Tuning manual, Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick (O’Reilly & Associates, 1998), or Oracle SQL: The Essential Reference by David Kreines (O’Reilly & Associates, 2000).

The plan table used by EXPLAIN PLAN is most often named PLAN_TABLE. The columns of the table describe various aspects of the execution plan. Two columns are of special importance for parallel execution:

OBJECT_NODE

Describes the order in which the output from execution plan operations is consumed.

OTHER_TAG

Describes the parallel or serial relationships between steps. The possible values for the OTHER_TAG column are described in Table 5.4.

Table 5-4. Values of the OTHER_TAG Column in the Plan Table

Value	Meaning
SERIAL	A serial operation
No value; the column is null	A serial operation
SERIAL_FROM_REMOTE	A serial operation at a remote site
PARALLEL_FROM_SERIAL	A serial operation that passes output ...