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

Table 8-2. ( continued )
Id Operation Description

PX RECEIVE 22 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

23 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

24 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 PART tables, and the optimizer
required several iterations to complete the operation.

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

output previously).

25

PX RECEIVE 26 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 HASH

27 Maps rows to individual PX servers using a hash function on the join key.

This method helps to achieve an equal distribution of work for individual PX
servers. The optimizer uses this method of redistribution because of the GROUP
BY clause in the statement.

PX BLOCK ITERATOR 28 Oracle is accessing the blocks in granule units. This step basically indicates

that the tables have a large volume of data in the PARTSUPP tables, and the
optimizer required several iterations to complete the operation.

TABLE ACCESS FULL Producer for the PARTSUPP table (illustrated in the Name e column in the

XPLAN output previously).

29

Parallel Processing Parameters
Not every query or operation is ideal for a parallel operation. Parallelism could be set at the object level by setting
DOP (threads) for the object or by using hints. Apart from this there are certain important parameters that have to be
understood and used to take advantage of the full potential of the parallelism feature. A few of these parameters were
discussed earlier in the “Initialization Parameters” section, and a few more are discussed in this section.

PARALLEL_DEGREE_POLICY
This parameter is new in Oracle Database 11g Release 2. It drives how parallelism is activated in the system. Whereas
there are several methods to enable parallelism , this parameter basically helps implement them. The value for this
parameter could be either AUTO when all statements are executed in parallel, or MANUAL when parallelism is only
implemented when specific methods described under “methods of enabling parallelism” are used.