当前位置:Gxlcms > mysql > 性能陷阱:Oracle表连接中范围比较

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

时间:2021-07-01 10:21:17 帮助过:30人阅读

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 <= 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.

linux

人气教程排行