性能陷阱：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. Oracle数据库提升效率，用3PAR浅谈Oracle性能优化可能出现的问题相关资讯 oracle

[INS-32052] Oracle基目录和Oracle （07/22/2014 07:41:41）
Oracle 4个大对象（lobs）数据类型（02/03/2013 12:33:05）
Oracle按时间段分组统计（07/26/2012 10:36:48）

[Oracle] dbms_metadata.get_ddl的（07/12/2013 07:37:30）
Liferay Portal 配置使用Oracle和（07/31/2012 20:07:18）
Concurrent Request:Inactive （07/20/2012 07:44:05）

本文评论查看全部评论（0）