还是上篇提到的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
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 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 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/