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

The QC process instructs the PX processes to retrieve data in granule units. PX processes execute the operation
one granule at a time. When an execution server finishes reading the rows corresponding to a granule, and when there
are more granules to be retrieved, it obtains another granule from the coordinator. This operation continues until the
table has been completely read.
The two parallel execution tiers are related to each other; all the parallel execution servers involved in the scan
operation send rows to the appropriate parallel execution server performing the sort operation. For example, if a
row scanned by a parallel execution server contains a value of the PRODUCT_NAME column between A and G, that row
gets sent to the first ORDER BY parallel execution server. When the scan operation is complete, the sorting processes
can return the sorted results to QC. The QC then assembles the pieces into the desired full table scan and returns the
results to the user.
With underlying data structures—for example, if the data is partitioned or sub-partitioned to spread data into
separate data files—the PX servers can benefit from these structures by leveraging individual partitions as granules
(called partition-based granules). In this case, only one PX server performs the work for all data in a single data
partition. When generating the explain plan for the query, the optimizer would consider partition-based granules if
the number of partitions or sub-partitions accessed in the operation is at least equal to the DOP. 3

Parallel Processing in RAC
All the features and functions described previously also apply to a RAC environment. As illustrated in Figure 8-6 ,
parallel processing in a RAC environment behaves exactly the same way as a parallel execution process on a single
instance, with one difference in the case of a RAC environment: slaves can execute on multiple nodes that are
participating as members in the cluster. The server subdivides the statement into smaller operations that run against a
common database residing on a shared disk.

User process

Query Coordinator

PX Slave PX Slave PX Slave PX Slave

Database Table

Figure 8-6. Parallel processing in RAC

Several CPUs on multiple nodes can each scan part of the table in parallel and aggregate the results. Participation
of multiple nodes in a parallel execution task depends on the volume of work being handled by each of the servers.
If the load on the system is minimal, the work will be spread across as many servers as determined by the optimizer
when generating the DOP for the operation during execution plan generation. If the system is fully loaded, then a few
local servers will be used to minimize any additional overhead required to coordinate local processes and to avoid any
inter-instance overhead.

3 “Parallel Execution Fundamentals in Oracle Database 11g Release 2,” an Oracle White Paper, November 2009.