Oracle执行计划中 并行和BUFFER SORT的问题(3)

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

| 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并行是如何来的，一直没有重现出。

本文出自 “srsunbing” 博客，请务必保留此出处http://srsunbing.blog.51cto.com/3221858/1630138