熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

性能陷阱:Oracle表連接中范圍比較

2013-11-13 22:22:05  來源: Oracle 

  Lately I met a case that the range filter predicates due to wrong cardinality issue Lets check the following query

  最近遇到一個由於范圍過濾導致錯誤基數而引起的性能問題讓我們來看下面的查詢

  The real records number is around

  真實的記錄數大約百萬

  The explain plan shows optimizer think it has good filtration So put this JOIN in the first order Actually it is totally wrong

  執行計劃顯示這裡優化器認為它有良好的過濾芯所以把它放在一個多個表JOIN的第一位置顯然它完全錯了

  SQL> set autotrace traceonly explain;

  SQL> set linesize

  SQL> SELECT

     TDURATIONSECSQTY TIMEINSECONDS

  TMONEYAMT MONEYAMOUNT

  TWAGEAMT WAGEAMOUNT

  TAPPLYDTM APPLYDATE

  TADJAPPLYDTM ADJUSTEDAPPLYDATE

  TSTARTDTM

  TENDDTM

  THOMEACCOUNTSW

  FROM

  TKCSOWNERWFCTOTAL     T

  TKCSOWNERPAYCODEMMFLAT MP

  WHERE

  MPEFFECTIVEDTM <= TAPPLYDTM

  AND MPEXPIRATIONDTM > TAPPLYDTM

  AND MPPAYCODEID = TPAYCODEID

  /

  

  | Id | Operation       | Name       | Rows | Bytes | Cost |

  

  |   | SELECT STATEMENT   |           | | K| |

  |* | HASH JOIN       |           | | K| |

  |   |   TABLE ACCESS FULL| PAYCODEMMFLAT |   | |   |

  |   |   TABLE ACCESS FULL| WFCTOTAL     | K|   M| |

  Now let me comment the range filter

  讓我注釋到范圍條件看

  MPEFFECTIVEDTM <= TAPPLYDTM

  AND MPEXPIRATIONDTM > TAPPLYDTM

  SQL> SELECT

     TDURATIONSECSQTY TIMEINSECONDS

  TMONEYAMT MONEYAMOUNT

  TWAGEAMT WAGEAMOUNT

  TAPPLYDTM APPLYDATE

  TADJAPPLYDTM ADJUSTEDAPPLYDATE

  TSTARTDTM

  TENDDTM

  THOMEACCOUNTSW

  FROM

  TKCSOWNERWFCTOTAL     T

  TKCSOWNERPAYCODEMMFLAT MP

  WHERE

  /*   MPEFFECTIVEDTM <= TAPPLYDTM

  AND MPEXPIRATIONDTM > TAPPLYDTM*/

  MPPAYCODEID = TPAYCODEID                                       

   /

  Execution Plan

  

  Plan hash value:

  

  | Id | Operation         | Name         | Rows | Bytes | Cost |

  

  |   | SELECT STATEMENT     |             |   M| M| |

  |* | HASH JOIN         |             |   M| M| |

  |   |   INDEX FAST FULL SCAN| PK_PAYCODEMMFLAT |   | |   |

  |   |   TABLE ACCESS FULL   | WFCTOTAL       | K|   M| |

  The Cardinality show it is already close to the correct value

  基礎是已經接近正確結果了

  So how optimizer work out the cardinality with range filter in TABLE JOIN ?

  那麼優化器怎麼出來表連接中的范圍掃描呢?

  The answer is % always %

  答案是%

   * % * % = This is exact equal to the result of test

  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


From:http://tw.wingwit.com/Article/program/Oracle/201311/18890.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.