性能陷阱:Oracle表连接中范围比较
Lately, I met a case that the range filter predicates due to wrong cardinality issue. Letrsquo;s check the followin
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
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
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
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.
下一篇: 逆波兰表达式学习日记(2)