遭遇Bug 5766310 Bad join cardinality is in the presence of histograms

還是上篇提到的SQL

http://sundog315.itpub.net/post/308/524769

修改完畢後，在某一特定伺服器，效能仍然存在問題，看了一下執行計劃，確實是有問題的

擷取相關的執行計劃

35 | HASH GROUP BY | | 1 | 150 | 398 (2)| 00:00:05 | | |
|* 36 | FILTER | | | | | | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | SELLPAYGOODS | 1 | 34 | 3 (0)| 00:00:01 | | |
| 38 | NESTED LOOPS | | 1 | 150 | 397 (1)| 00:00:05 | | |
| 39 | NESTED LOOPS | | 1 | 116 | 394 (1)| 00:00:05 | | |
| 40 | NESTED LOOPS | | 6 | 546 | 382 (1)| 00:00:05 | | |
| 41 | MERGE JOIN CARTESIAN | | 1 | 45 | 336 (1)| 00:00:05 | | |
|* 42 | TABLE ACCESS FULL | GOODSMFRAME | 1 | 25 | 333 (1)| 00:00:04 | | || 43 | BUFFER SORT | | 1 | 20 | 3 (0)| 00:00:01 | | |
|* 44 | TABLE ACCESS FULL | PAYMODE | 1 | 20 | 3 (0)| 00:00:01 | | |
| 45 | REMOTE | SELLDETAIL | 17 | 782 | 46 (0)| 00:00:01 | POS_DB | R->S |
| 46 | REMOTE | SELLHEAD | 1 | 25 | 2 (0)| 00:00:01 | POS_DB | R->S |
|* 47 | INDEX RANGE SCAN | PK_SELLPAYGOODS | 1 | | 2 (0)| 00:00:01 | | |

黑色部分是重要的部分，10053看一下

Column (#3): GMFMARKET(VARCHAR2)
AvgLen: 5.00 NDV: 36 Nulls: 0 Density: 3.6593e-06 Histogram: Freq #Bkts: 36 UncompBkts: 136637 EndPtVals: 36
Table: GOODSMFRAME Alias: GOODSMFRAME
Card: Original: 136637 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50

而實際上，這裡的資料量是1萬多條，差距太大了

應該是觸發了Bug 5766310 - Bad join cardinality is in the presence of histograms [ID 5766310.8]

刪除直方圖後，恢復了正常的執行計劃

Column (#3): GMFMARKET(VARCHAR2)
AvgLen: 5.00 NDV: 36 Nulls: 0 Density: 0.027778 Table: GOODSMFRAME Alias: GOODSMFRAME
Card: Original: 136661 Rounded: 3796 Computed: 3796.14 Non Adjusted: 3796.14

[@more@]

Bug 5766310 Bad join cardinality is in the presence of histograms

This note gives a brief overview of bug 5766310.
The content was last updated on: 01-DEC-2009
Click for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11
Versions confirmed as being affected
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

Symptoms:	Related To:

Description

The presence of frequency histogram can lead to 
low cardinality estimation and a suboptimal execution plan.

This fix changes the way density is calculated with height balance histograms too;
and with data skewness and almost-popular values can underestimate cardinalities.


Workaround
  Drop the frequency histogram that is on the predicate column.

Note: This fix is disable by _fix_control of 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References

Bug:5766310 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article