ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Understanding Parallel Execution Plan

Understanding Parallel Execution Plan

原创 Linux操作系统 作者:chncaesar 时间:2013-09-23 00:57:05 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899703