Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 251

Table 8-2. ( continued )

Id Operation Description

TABLE ACCESS FULL Producer for the REGION table (illustrated in the Name column in XPLAN output

previously).

10

HASH JOIN This is a join operation between the SUPPLIER table and the NATION table using

the NATIONKEY column in the respective tables. These columns are identified
by S_NATIONKEY and N_NATIONKEY .

11

BUFFER SORT 12 The optimizer is using a buffering mechanism of a traditional sort operation;

however, there is no sort operation involved (the query does not contain an
ORDER BY operator). In this specific case, the optimizer is buffering the data to
avoid multiple tabescans against real data blocks. 6

PX RECEIVE 13 The consumers receive the data collected by the producers, in this case

to group them before transferring the data to the coordinator. As noticed,
for every producer operation there is a consumer operation. The example
involves a four table join, and there are four sets of producers and consumers.

PX SEND BROADCAST

14 Due to a larger variation in the number of the rows of the tables involved in the

operation, the optimizer is using the broadcast method to redistribute data to
the parallel execution servers. Instead of redistributing rows from both result
sets, the database sends the smaller result set to all PX servers to guarantee the
individual servers are able to complete their join operation.

TABLE ACCESS FULL Producer for the NATION table (illustrated in the Name column in XPLAN output

previously).

15

HASH JOIN This is a join operation between the PARTSUPP table and the SUPPLIER table

using the SUPPKEY column in the respective tables. These columns are
identified by PS_SUPPKEY and S_SUPPKEY .

16

PX RECEIVE

17 The consumers receive the data collected by the producers in this case

to group them before transferring the data to the coordinator. As noticed
for every producer operation there is a consumer operation. The example
involves a four table join, and there are four sets of producers and consumers.

PX SEND BROADCAST 18 Due to a larger variation in the number of the rows of the tables involved in the

operation, the optimizer is using the broadcast method to redistribute data to
the parallel execution servers. Instead of redistributing rows from both result
sets, the database sends the smaller result set to all PX servers to guarantee the
individual servers are able to complete their join operation.

PX BLOCK ITERATOR 19 Oracle is accessing the blocks in granule units. In this case, the block range

granule over partition granules are used. This step basically indicates that the
tables have a large volume of data in the SUPPLIER table and the optimizer
required several iterations to complete the operation.

TABLE ACCESS FULL Producer for the SUPPLIER table (illustrated in the Name column in the XPLAN

output previously).

20

HASH JOIN This is a join operation between the PARTSUPP table and the PART table using

the PARTKEY column in the respective tables. These columns are identified by
PS_PARTKEY and P_PARTKEY .

21

( continued )

6 Ibid.