性能陷阱：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]