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
