近日开发说某个系统上有个sql执行时间忽快忽慢，让我帮忙看下，此sql是4个表（2个千万，2个十万）进行inner join操作，最后进行count(*)聚合操作，执行时间1--10S不等。查看执行计划发现使用了PX并行和BUFFER SORT操作，难怪忽快忽慢的，但是sql并没有显式加parallel，参数parallel_server也没有启用，这个并行和BUFFER SORT是从那来的呢？

创新互联建站坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站建设、网站设计、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的宕昌网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

下面通过实验来重现上面的情况：

1. PX并行和BUFFER SORT：

select /*+ parallel(e 4) parallel(d 4) */ e.ename, d.dname

  from scott.emp e, scott.dept d,scott.emp m

 where e.deptno = d.deptno

   and d.deptno = m.deptno

   and e.deptno = 10;

Execution plan:

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

| Id  | Operation                  | Name     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT           |          |        |      |            |

|   1 |  PX COORDINATOR            |          |        |      |            |

|   2 |   PX SEND QC (RANDOM)      | :TQ10003 |  Q1,03 | P->S | QC (RAND)  |

|*  3 |    HASH JOIN BUFFERED      |          |  Q1,03 | PCWP |            |

|   4 |     PX RECEIVE             |          |  Q1,03 | PCWP |            |

|   5 |      PX SEND BROADCAST     | :TQ10001 |  Q1,01 | S->P | BROADCAST  |

|   6 |       PX SELECTOR          |          |  Q1,01 | SCWC |            |

|   7 |        TABLE ACCESS FULL   | EMP      |  Q1,01 | SCWP |            |

|*  8 |     HASH JOIN              |          |  Q1,03 | PCWP |            |

|   9 |      JOIN FILTER CREATE    | :BF0000  |  Q1,03 | PCWP |            |

|  10 |       BUFFER SORT          |          |  Q1,03 | PCWC |            |

|  11 |        PX RECEIVE          |          |  Q1,03 | PCWP |            |

|  12 |         PX SEND HYBRID HASH| :TQ10000 |        | S->P | HYBRID HASH|

|* 13 |          TABLE ACCESS FULL | DEPT     |        |      |            |

|  14 |      PX RECEIVE            |          |  Q1,03 | PCWP |            |

|  15 |       PX SEND HYBRID HASH  | :TQ10002 |  Q1,02 | P->P | HYBRID HASH|

|  16 |        JOIN FILTER USE     | :BF0000  |  Q1,02 | PCWP |            |

|  17 |         PX BLOCK ITERATOR  |          |  Q1,02 | PCWC |            |

|* 18 |          TABLE ACCESS FULL | EMP      |  Q1,02 | PCWP |            |

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

2. BUFFER SORT（积卡尔积会产生这个）：

select e.ename, d.dname

  from scott.emp e, scott.dept d;

Execution plan:

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

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

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

|   0 | SELECT STATEMENT       |             |      |       |11 (100)|          |

|   1 |  MERGE JOIN CARTESIAN  |             |  95  | 57780 |11   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL    | DEPT        |    5 |   324 | 2   (0)| 00:00:01 |

|   3 |   BUFFER SORT          |             |   19 |   856 | 9   (0)| 00:00:01 |

|   4 |    INDEX FAST FULL SCAN| PK_EMP      |   19 |   856 | 0   (0)|          |

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

查看Oracle的解释：

   The BUFFER SORT operation indicates that the database is copying the data blocks obtained by the scan of pk_emp from the SGA to the PGA. This strategy avoids multiple scans of the same blocks in the database buffer cache, which would generate many logical reads and permit resource contention.

   最后的解决方法：给其中的2个小表加上rowid >= '0'的条件，让表通过index rowid扫描走hash join连接，稳定在1S内返回结果。

疑问：原sql的PX并行是如何来的，一直没有重现出。

网页题目：Oracle执行计划中并行和BUFFERSORT的问题
分享链接：http://scyingshan.cn/article/pjppsj.html