（原创）性能陷阱：Oracle表连接中范围比较

三里屯摇滚

（原创）性能陷阱：Oracle表连接中范围比较

（原创）性能陷阱：Oracle表连接中范围比较 作者：文杰 Valen Wong 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. 我丝毫不会惊讶如果遇到任何与此相关的性能问题。我想Oracle公司需要持续改进CBO。

小忻黑夜

谢谢分享