Database Reference
In-Depth Information
QC
Group by
operation
Q1,02
PQ distribution
HASH
Q1,01
HASH join
PQ distribution
Broadcast
Table scan
Q1,00
Sales
Table scan
Products
Figure 12-1.
PX operational details
With BROADCAST, all rows are distributed from one PX server set to another PX server set. With the HASH
distribution mechanism, a subset of rows is distributed from one PX server set to another PX server set, that is, each
PX server process receives a subset of row pieces. In the preceding example, each PX server process of Q1,02 will
receive 1/8th of the rows produced in step 6 of the execution plan.
You must understand that there can be at most two sets of PX processes active at any time during a SQL
execution, even though the execution plan shows more than two PX server sets. PX server processes can be reused to
perform another operation in a different part of the execution plan. In this example, PX processes associated with PX
server set Q1,00 are reassigned as PX server set Q1,02 in step 3.
Listing 12-2 shows the PX execution in play for the query in Listing 12-1. Parallel statement execution with a
degree of parallelism (DOP) of 8 employed 16 PX server processes. Notice that the
server_set
column is showing the
PX server set. Essentially, there are two sets of PX server processes executing this statement.
Listing 12-2.
PX Server Processes in Single Instance: Script pxslaves.sql
col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col server_set for A10
set pages 100
select
decode(px.qcinst_id,NULL,username,
