Database Reference
In-Depth Information
The following execution plan shows an example of the processing illustrated in Figure
15-6
:
SELECT * FROM t
----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | Q1,00 | PCWP | |
----------------------------------------------------------------------
The execution plan is executed in the following way:
Through operation 4 (
TABLE ACCESS FULL
), each slave process scans part of the table.
Which part it scans depends on its parent operation, 3 (
PX BLOCK ITERATOR
). This is the
operation related to block range granules.
1.
Operation 2 (
PX SEND QC
) sends the retrieved data to the query coordinator.
2.
The query coordinator, through operation 1 (
PX COORDINATOR
), receives data from the
slave processes and sends it back to the client.
3.
When communication between processes take place, the processes that send data are called
producers
, and the
processes that receive data are called
consumers
. To send data, a producer writes into a queue known as
table queue
.
To receive data, a consumer reads from a table queue. Depending on the operation executed by the producers and
the consumers, rows are distributed using one of the following methods (in the
dbms_xplan
package output, the
PQ Distrib
column provides this information):
•
Broadcast
: Each producer sends all rows to each consumer.
•
Broadcast Local
: This is a variation of the Broadcast distribution. It's used to send all rows to a
subset of the slave processes only. It's most useful in a RAC environment to minimize cross-
instance communication.
•
Round-robin
: Producers send each row to a single consumer one at a time, like dealing cards.
As a result, rows are evenly distributed among consumers.
•
Range
: Producers send specific ranges of rows to different consumers. Dynamic range
partitioning is performed to determine which row has to be sent to which consumer. For
example, for a sort, this method range partitions the rows based on the columns used in the
ORDER BY
clause, so that each consumer can order only its subset of rows.
•
Hash
: Producers send rows to consumers as determined by a hash function. Dynamic hash
partitioning is performed to determine which row is to be sent to which consumer. For
example, for an aggregation, this method may hash partition the rows based on the columns
used in the
GROUP BY
clause.
•
Partition Key
: Producers send rows to consumers based on the partition key. For addition
information, refer to the “Partial Partition-wise Joins” section in Chapter 14.
•
Hybrid Hash
: Producers send rows to consumers using either the Broadcast or the Hash
distribution method. Which one of the two is used is determined at runtime. Available from
