先看下以下实验

SQL> select /*+parallel(test_a,3)*/count(*) from test_a;   --px执行

执行计划
----------------------------------------------------------
Plan hash value: 2663325361

--------------------------------------------------------------------------------

------------------------

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T

Q  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

------------------------

|   0 | SELECT STATEMENT       |          |     1 |   559   (7)| 00:00:07 |
   |      |            |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |
   |      |            |

|   2 |   PX COORDINATOR       |          |       |            |          |
   |      |            |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,

00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,

00 | PCWP |            |

|   5 |      PX BLOCK ITERATOR |          |  4034K|   559   (7)| 00:00:07 |  Q1,

00 | PCWC |            |

|   6 |       TABLE ACCESS FULL| TEST_A   |  4034K|   559   (7)| 00:00:07 |  Q1,

00 | PCWP |            |

--------------------------------------------------------------------------------

------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
       1901  recursive calls             

       4  db block gets
       7045  consistent gets
       7060  physical reads     --direct path read
        664  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         41  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from test_a;

执行计划
----------------------------------------------------------
Plan hash value: 2187875359

---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |  1518   (8)| 00:00:19 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_A |  4034K|  1518   (8)| 00:00:19 |
---------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
       1077  recursive calls
          0  db block gets
       6643  consistent gets
       6409  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         28  sorts (memory)
          0  sorts (disk)
          1  rows processed

从上面的对比中，我们可以发现 px执行比一般的query 多了 recursive calls，而consistent gets是差不多的
recursive calls 多了近一倍！而我们还可以发现，执行完px后，接着再执行 normal query，physical reads没变，说明了px 不存在在buffer cache中，也证实了 direct path read

上面是一种现象。
还有一种现象，Oracle uses message buffers to pass data between the various processes during inter-operational parallel operations
inter-operational parallel 就是 各个slave processes 并行执行（ there are three threads of execution, each of which has one PX slave process.  This is referred as intra-operational parallelism），
那我们知道，oracle 通过 message pool 来传递数据
These buffers reside in the shared pool inside the SGA if PARALLEL_AUTOMATIC_TUNING is set to FALSE, and in the large pool if the parameter value is set to a value of TRUE.  A fixed number of buffers is required for each producer-consumer connection
producer-consumer 模式就是以下的的几种模式：
 when executing either a merge or hash join, or when sorting or other aggregation operations are present in the original query.  It will also occur when a parallel DML statement is executed with a correlated parallel SELECT statement
那以下几种模式在执行计划中，会有
 PX send
这个出现，也就是说，比如 SELECT /*+parallel(test_d,2)*/COUNT(*),owner FROM test_d GROUP BY owner;
看执行计划
SQL> SELECT /*+parallel(test_d,8)*/COUNT(*),owner FROM test_d GROUP BY owner;

已选择27行。

执行计划
----------------------------------------------------------
Plan hash value: 519988703

--------------------------------------------------------------------------------

----------------------------------

| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time
    |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

----------------------------------

|   0 | SELECT STATEMENT         |          |    27 |   162 |    24   (9)| 00:00

:01 |        |      |            |

|   1 |  PX COORDINATOR          |          |       |       |            |
    |        |      |            |

|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    27 |   162 |    24   (9)| 00:00

:01 |  Q1,01 | P->S | QC (RAND)  |

|   3 |    HASH GROUP BY         |          |    27 |   162 |    24   (9)| 00:00

:01 |  Q1,01 | PCWP |            |

|   4 |     PX RECEIVE           |          |    27 |   162 |    24   (9)| 00:00

:01 |  Q1,01 | PCWP |            |

|   5 |      PX SEND HASH        | :TQ10000 |    27 |   162 |    24   (9)| 00:00    ---注意这里，有一个send

:01 |  Q1,00 | P->P | HASH       |

|   6 |       HASH GROUP BY      |          |    27 |   162 |    24   (9)| 00:00

:01 |  Q1,00 | PCWP |            |

|   7 |        PX BLOCK ITERATOR |          | 50670 |   296K|    22   (0)| 00:00

:01 |  Q1,00 | PCWC |            |

|   8 |         TABLE ACCESS FULL| TEST_D   | 50670 |   296K|    22   (0)| 00:00

:01 |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

----------------------------------

统计信息
----------------------------------------------------------
        354  recursive calls
          3  db block gets
        957  consistent gets
        696  physical reads
        624  redo size
        972  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         27  rows processed

另外看px的dml  ---在 parallel dml 下执行

insert /*+parallel(test_b,2)*/into test_b select /*+parallel(a,2)*/ * from
  test_a a

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          2          0           0
Execute      1      0.01      12.68          0         21        306     4194304
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03      12.70          0         23        306     4194304

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  PX COORDINATOR  (cr=10 pr=0 pw=0 time=12166357 us)
      0   PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
      0    LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      0     PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0      PX SEND ROUND-ROBIN :TQ10000 (cr=0 pr=0 pw=0 time=0 us)   ---注意这里的send
      0       PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0        TABLE ACCESS FULL TEST_A (cr=0 pr=0 pw=0 time=0 us)
     

象这种 query 语句，其实它 起了 4 个slave processes，两个 processes 去执行 direct path read，并且会过滤掉where 条件，就是所谓的consumer  ，然后，另外两个 slave processes 去 RECEIVE 上面所产生的结果集，而这部分数据传递的信息（注意不是数据，因为通过大批量的insert后观察v$px_process_sysstat，发现 buffer变化不是很大）就放在message pool中，而message pool的存放位置9i后应该是shared_pool中了（PARALLEL_AUTOMATIC_TUNING = FALSE）

分配slave进行需要在shared pool中先获得空间（PARALLEL_AUTOMATIC_TUNING = FALSE），如果shared pool空间不足，会导致slave进程失败或死掉，造成ACK不能返回  --坛友写的，关注下

    乱谈，见笑！