性能陷阱：Oracle表連接中范圍比較

Lately, I met a case that the range filter predicates due to wrong cardinality issue. Let’s check the following query.

　　最近遇到一個由于范圍過濾導致錯誤基數而引起的性能問題。讓我們來看下面的查詢：

　　The real records number is around 38,000,000.
-
 

　　真實的記錄數大約3千8百萬

　　The explain plan shows 72838, optimizer think it has good filtration. So put this JOIN in the first order. Actually , it is totally wrong.

　　執行計劃顯示72838，這里優化器認為它有良好的過濾芯，所以把它放在一個多個表JOIN的第一位置。顯然，它完全錯了。

　　SQL> set autotrace traceonly explain;

　　SQL> set linesize 999

　　SQL> SELECT

　　2   T.DURATIONSECSQTY TIMEINSECONDS,

　　T.MONEYAMT MONEYAMOUNT,

　　T.WAGEAMT WAGEAMOUNT,

　　T.APPLYDTM APPLYDATE,

　　T.ADJAPPLYDTM ADJUSTEDAPPLYDATE,

　　T.STARTDTM,

　　T.ENDDTM,

　　T.HOMEACCOUNTSW

　　FROM

　　TKCSOWNER.WFCTOTAL     T,

　　TKCSOWNER.PAYCODE1MMFLAT MP

　　WHERE

　　MP.EFFECTIVEDTM <= T.APPLYDTM

　　AND MP.EXPIRATIONDTM > T.APPLYDTM

　　AND MP.PAYCODEID = T.PAYCODEID

　　/

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

　　| Id | Operation       | Name       | Rows | Bytes | Cost |

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

　　|   0 | SELECT STATEMENT   |           | 72838 | 5192K| 37450 |

　　|* 1 | HASH JOIN       |           | 72838 | 5192K| 37450 |

　　|   2 |   TABLE ACCESS FULL| PAYCODE1MMFLAT |   323 | 6783 |   3 |

　　|   3 |   TABLE ACCESS FULL| WFCTOTAL     | 8938K|   443M| 37317 |

　　Now, let me comment the range filter.

　　讓我注釋到范圍條件看：

　　“MP.EFFECTIVEDTM <= T.APPLYDTM

　　AND MP.EXPIRATIONDTM > T.APPLYDTM”

　　SQL> SELECT

　　2   T.DURATIONSECSQTY TIMEINSECONDS,

　　T.MONEYAMT MONEYAMOUNT,

　　T.WAGEAMT WAGEAMOUNT,

　　T.APPLYDTM APPLYDATE,

　　T.ADJAPPLYDTM ADJUSTEDAPPLYDATE,

　　T.STARTDTM,

　　T.ENDDTM,

　　T.HOMEACCOUNTSW

　　FROM

　　TKCSOWNER.WFCTOTAL     T,

　　TKCSOWNER.PAYCODE1MMFLAT MP

　　WHERE

　　/*   MP.EFFECTIVEDTM <= T.APPLYDTM

　　AND MP.EXPIRATIONDTM > T.APPLYDTM*/

　　MP.PAYCODEID = T.PAYCODEID 3   4   5   6   7   8   9   10   11   12   13   14   15   16

　　17 /

　　Execution Plan

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

　　Plan hash value: 564403449

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

　　| Id | Operation         | Name         | Rows | Bytes | Cost |

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

　　|   0 | SELECT STATEMENT     |             |   29M| 1583M| 37405 |

　　|* 1 | HASH JOIN         |             |   29M| 1583M| 37405 |

　　|   2 |   INDEX FAST FULL SCAN| PK_PAYCODE1MMFLAT |   323 | 1615 |   1 |

　　|   3 |   TABLE ACCESS FULL   | WFCTOTAL       | 8938K|   443M| 37317 |

　　The Cardinality show 29,135,142 , it is already close to the correct value.

　　基礎是29,135,142，已經接近正確結果了。

　　So how optimizer work out the cardinality with range filter in TABLE JOIN ?

　　那么優化器怎么出來表連接中的范圍掃描呢？

　　The answer is 5%, always 5%.

　　答案是5%

　　29135142 * 5% * 5% = 72837.8 , This is exact equal to the result of test 1.

　　So if you meet any performance issue with range filter in TBALE JOIN, I am not surprise. I think Oracle need to improve the CBO to get better support on such situation.

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]