Dividing Work Among Parallel Execution Servers

The parallel execution coordinator examines each operation in a SQL statement's execution plan then determines the way in which the rows operated on by the operation must be divided or redistributed among the parallel execution servers. As an example of parallel query, consider the query in Example 8-1.

In Example 8-1 a hint has been used in the query to specify the DOP of the tables customers and sales.

Example 8-2 shows the explain plan output for the query in Example 8-1.

Figure 8-1 illustrates the data flow or query plan for the query in Example 8-1.

Figure 8-1 Data Flow Diagram for Joining Tables

Description of
Description of "Figure 8-1 Data Flow Diagram for Joining Tables"

Example 8-1 Running an Explain Plan for a Query on Customers and Sales

EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name, 
  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sales, customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;

Explained.

Example 8-2 Explain Plan Output for a Query on Customers and Sales

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 4060011603
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |   925 | 25900 |        |      |            |
|   1 |  PX COORDINATOR            |           |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10003  |   925 | 25900 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY           |           |   925 | 25900 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE             |           |   925 | 25900 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH          | :TQ10002  |   925 | 25900 |  Q1,02 | P->P | HASH       |
|*  6 |       HASH JOIN BUFFERED   |           |   925 | 25900 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE          |           |   630 | 12600 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10000  |   630 | 12600 |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |           |   630 | 12600 |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL| CUSTOMERS |   630 | 12600 |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE          |           |   960 |  7680 |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10001  |   960 |  7680 |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |           |   960 |  7680 |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL| SALES     |   960 |  7680 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")
 
26 rows selected.