Database Reference
In-Depth Information
2.
The SALES
table is scanned by a set of PX server processes marked as Q1,01 in the TQ
column. In addition to the scanning SALES table, this PX server set receives the row pieces
of the PRODUCTS table sent by PX server set Q1,00. In this HASH join step, PX server set
Q1,01 acts as a consumer.
3.
After completing the HASH join operation, the Q1,01 PX server set processes redistribute
the row pieces to the next set of PX server processes, marked as Q1,02 in the TQ column.
PX server set Q1,02 performs a
HASH GROUP BY
operation. For the
HASH GROUP BY
operation, PX server set Q1,01 acts as a producer and the Q1,02 PX server set operates as a
consumer.
4.
PX server set Q1,02 completes grouping operations and sends the processed data to QC.
Listing 12-1.
Parallel SQL Execution Plan
explain plan FOR
SELECT /*+ parallel (8) full (p) full (s) */
prod_name, SUM (QUANTITY_SOLD) FROM sales s , products p
WHERE s.prod_id = p.prod_id
group by prod_name HAVING SUM(quantity_sold)>100
/
Select * from table(dbms_xplan.display('','','BASIC +parallel'));
------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
| 3 | FILTER | | Q1,02 | PCWC | |
| 4 | HASH GROUP BY | | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH |
| 7 | HASH GROUP BY | | Q1,01 | PCWP | |
| 8 | HASH JOIN | | Q1,01 | PCWP | |
| 9 | PX RECEIVE | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST |
| 11 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 12 | TABLE ACCESS FULL| PRODUCTS | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | SALES | Q1,01 | PCWP | |
------------------------------------------------------------------------------
The parallel query operation discussed earlier is shown in Figure
12-1
. In the figure, PX servers Q1,00, Q1,01, and
Q1,02 are drawn as nodes of a PX tree. The distribution between PX servers is represented as a connection between
the nodes of the PX tree. The PX Distribution column prints the method of row distribution between PX server sets.
In Listing 12-1, at step 10, PX distribution is set to
BROADCAST
, indicating that all rows of the PRODUCTS table are
distributed from the Q1,00 PX server set to all PX server processes of the Q1,01 PX server set. (Broadcast mechanism
can be chosen if the row source is smaller.) PX server processes of Q1,01 PX server set receive the PRODUCTS table
and then join it to the SALES table to complete join processing. Notice that in step 6, the Distribution mechanism is
HASH—a hashing algorithm applied to grouping columns—and rows are distributed to PX server processes of the
Q1,02 PX server set.
