遭遇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 here 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	10.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

10.2.0.2 Patch 15 on Windows Platforms 10.2.0.4 (Server Patch Set)

Symptoms:	Related To:
Performance Of Query/ies Affected Optimizer Bad Cardinality	Optimizer

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 bug:5483301

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-1056127/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-1056127/