Oracle執行計畫中 並行和BUFFER SORT的問題

標籤：buffer sort

   近日開發說某個系統上有個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並行是如何來的，一直沒有重現出。

本文出自 “srsunbing” 部落格，請務必保留此出處http://srsunbing.blog.51cto.com/3221858/1630138

Oracle執行計畫中 並行和BUFFER SORT的問題