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

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

　　最近遇到一個(gè)由于范圍過(guò)濾導(dǎo)致錯(cuò)誤基數(shù)而引起的性能問(wèn)題。讓我們來(lái)看下面的查詢：

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

　　真實(shí)的記錄數(shù)大約3千8百萬(wàn)

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

　　執(zhí)行計(jì)劃顯示72838，這里優(yōu)化器認(rèn)為它有良好的過(guò)濾芯，所以把它放在一個(gè)多個(gè)表JOIN的第一位置。顯然，它完全錯(cuò)了。

　　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.

　　基礎(chǔ)是29,135,142，已經(jīng)接近正確結(jié)果了。

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

　　那么優(yōu)化器怎么出來(lái)表連接中的范圍掃描呢？

　　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.

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]