欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

性能陷阱:Oracle表连接中范围比较

程序员文章站 2022-06-15 12:18:00
...

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.

性能陷阱:Oracle表连接中范围比较