Understanding Parallel Execution Plan

chncaesar發表於2013-09-23
Parallel
The execution plan:

SQL_ID  fjwcnz05c5ugv, child number 0
-------------------------------------
select /*+ PARALLEL(4) */ COUNT(order_id), CUSTOMER_ID from orders 
GROUP BY CUSTOMER_ID
 
Plan hash value: 1771920570
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |       |       |       |   911 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |   100K|   984K|       |   911   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          |   100K|   984K|    19M|   911   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |   100K|   984K|       |   911   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |   100K|   984K|       |   911   (1)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          |   100K|   984K|    19M|   911   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  1000K|  9765K|       |   295   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| ORDERS   |  1000K|  9765K|       |   295   (1)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - Degree of Parallelism is 4 because of hint
 
 
1. Operation
HASH GROUP BY : Starting from 10g, Oracle uses a hash algorithm to "group by" instead of the sort algorith, it's CPU cost is lower.
PX CORDINATOR : The query coordinator.
PX SEND QC(RANDOM) : Parallel sends to Query Coordinator in random order.
PX BLOCK ITERATOR : Parallism graules in block
PX RECEIVE : PX receieves.
PX SEND HASH : PX sends in Hash
1. TQ
Each set of parallel processes gets a table queue, so this column indicates which set of parallel processes implement the activity specified in the "Operation" column. 
Q1,00 and Q1,01 indicate that there're two sets of process servers.
2. IN-OUT
PCWP: Parallel Combine with Parent. 
PCWC: Parallel Combine wth Child.  
P->S: Parallel to Serial.
S->P: Serial to Parallel.
P->P: Parallel to Parallel. Inter-operation parallism.
3. PQ Distrib
QC(Rand): All producers send their rows to the query coordinator in random order.
HASH: A hash function is used to determine which consumer a producer sends a row to. This is useful for hash joins. Q1,00 are producers, while Q1,01 consumers.
Range: Producers send rows in a different range to different consumers.  This is useful for an ORDER BY operation.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-773002/，如需轉載，請註明出處，否則將追究法律責任。

相關文章