参考文章：
http://blog.chinaunix.net/u2/68465/showart_683673.html
http://blog.chinaunix.net/u/10516/showart_1302846.html
一.CURSOR_SHARING介绍：
控制可以终止共享相同的共享游标的 SQL 语句类型。
EXACT 精确匹配，空格，字母大小写都严格匹配。
SIMILAR 将未使用绑定变量的语句转化为类似的使用绑定变量的SQL共享。但如果此SQL使用到了histogram(柱状图)来生成执行计划，那么就不会和类似的SQL共享了。
FORCE 和SIMIAR值差不多，只是如果SQL使用到了histogram也会采用和类似的SQL共享。
Oracle推荐使用SIMILAR，这样避免了一味地如force一样转换成变量形式，因为有histograms的情况下转换成变量之后就容易产生错误的执行计划，没有利用上统计信息。
而exact，在没有histograms的情况下也要分别产生硬解析，这样的话，由于执行计划不会受到数据分布的影响(因为没有统计信息)重新解析是没有实质意义的。而similar则综合了两者的优点。
如果应用程序没有使用绑定变量，而且修改应该程序的代价很大，那么有时候采用折中的方式，在Oracle上设置CURSOR_SHARING为FORCE。这样强制系统采用绑定变量。但是将参数设置为FORCE会导致很多的问题。
从Oracle9i开始，CURSOR_SHARING的值增加了一个SIMILAR，Oracle推荐使用SIMILAR来代替FORCE，当参数设置为SIMILAR时，Oracle不会强制将全部的变量进行绑定，而是根据一些预定义的设置进行判断。
无论是FORCE参数还是改进后的SIMILAR参数，都是解决绑定变量的替代方式，这些方式都可能带来一些bug以及很多未知的东西。只要有可能就应该通过修改程序的方法去设置绑定变量。
提示CURSOR_SHARING_EXACT
CURSOR_SHARING_EXACT是当cursor_sharing=smiler or cursor_sharing=force时使用, 不使用通过强制绑定bind产生的执行计划。
使用方法如：
select /*+ CURSOR_SHARING_EXACT */ from table1 where col=1;
在Oracle 10g中，如果在数据库中发现某个SQL有大量的version_count。那么可能是由于cursor_sharing=SIMILAR，在执行该SQL时用到了柱状图的统计量信息，导致大量的硬解析。实际中，不会出现数据的分布不均衡情况，所以可以禁用柱状图的统计收集或修改cursor_sharing=EXCAT 或FORCE。
二.histogram与10053介绍：
histogram是oracle为cbo提供更精确的成本估计而设计的一种直方图数据。histogram能提供列的数据分布，每次分析表后列的分布信息将会被保存在统计表里面，分析时默认的histogram size是75，意思就是采用75个buckets来表示数据分布。
histogram分为2种类型，基于高度的histogram和基于值的histogram
基于高度的histogram
当histogram buckets的数量少于列的distinct value时，oracle会采用基于高度的直方图反映数据分布，每个bucket容纳相同数量的值。
基本格式如下所示
SQL> column column_name format a20;
SQL> column ENDPOINT_ACTUAL_VALUE format a20;
SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
2 FROM DBA_HISTOGRAMS
3 WHERE TABLE_NAME ='TEST' AND COLUMN_NAME='OBJECT_ID'
4 ORDER BY ENDPOINT_NUMBER;
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
-------------------- --------------- -------------- --------------------
OBJECT_ID 29 1
OBJECT_ID 44 2
OBJECT_ID 59 3
OBJECT_ID 74 4
OBJECT_ID 75 76
这里'OBJECT_ID'列有从1到76不同的76个值，bucket数量为75个，所以采取了基于高度的直方图。
可以看到1的值占据了1－29号bucket，2的值占据了30－44号bucket,3的值占据了45-59号bucket,4的值占据了60－74号bucket，值5－76占据了75号bucket。所以值1占的比例最大。
基于值的histogram
当histogram buckets>=列的distinct values时，那么Oracle会使用基于值的histogram，每个值将会占据一个bucket，来看一下
SQL> column column_name format a20;
SQL> column ENDPOINT_ACTUAL_VALUE format a20;
SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
2 FROM DBA_HISTOGRAMS
3 WHERE TABLE_NAME ='TEST' AND COLUMN_NAME='OBJECT_ID'
4 ORDER BY ENDPOINT_NUMBER;
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
-------------------- --------------- -------------- --------------------
OBJECT_ID 19928 1
OBJECT_ID 29927 2
OBJECT_ID 39926 3
OBJECT_ID 49838 4
OBJECT_ID 49839 5
OBJECT_ID 49840 6
OBJECT_ID 49841 7
OBJECT_ID 49842 8
OBJECT_ID 49843 9
OBJECT_ID 49844 10
OBJECT_ID 49845 11
OBJECT_ID 49846 12
OBJECT_ID 49847 13
OBJECT_ID 49848 14
OBJECT_ID 49849 15
OBJECT_ID 49850 16
OBJECT_ID 49851 17
OBJECT_ID 49852 18
OBJECT_ID 49853 19
OBJECT_ID 49854 20
OBJECT_ID 49855 21
OBJECT_ID 49856 22
OBJECT_ID 49857 23
OBJECT_ID 49858 24
OBJECT_ID 49859 25
OBJECT_ID 49860 26
OBJECT_ID 49861 27
OBJECT_ID 49862 28
OBJECT_ID 49863 29
OBJECT_ID 49864 30
OBJECT_ID 49865 31
OBJECT_ID 49866 32
OBJECT_ID 49867 33
OBJECT_ID 49868 34
OBJECT_ID 49869 35
OBJECT_ID 49870 36
OBJECT_ID 49871 37
OBJECT_ID 49872 38
OBJECT_ID 49873 39
OBJECT_ID 49874 40
OBJECT_ID 49875 41
OBJECT_ID 49876 42
OBJECT_ID 49877 43
OBJECT_ID 49878 44
OBJECT_ID 49879 45
OBJECT_ID 49880 46
OBJECT_ID 49881 47
OBJECT_ID 49882 48
OBJECT_ID 49883 49
OBJECT_ID 49884 50
OBJECT_ID 49885 51
OBJECT_ID 49886 52
OBJECT_ID 49887 53
OBJECT_ID 49888 54
OBJECT_ID 49889 55
OBJECT_ID 49890 56
OBJECT_ID 49891 57
OBJECT_ID 49892 58
OBJECT_ID 49893 59
OBJECT_ID 49894 60
OBJECT_ID 49895 61
OBJECT_ID 49896 62
OBJECT_ID 49897 63
OBJECT_ID 49898 64
OBJECT_ID 49899 65
OBJECT_ID 49900 66
OBJECT_ID 49901 67
OBJECT_ID 49902 68
OBJECT_ID 49903 69
OBJECT_ID 49904 70
OBJECT_ID 49905 71
OBJECT_ID 49906 72
OBJECT_ID 49907 73
OBJECT_ID 49908 74
OBJECT_ID 49909 75
很明显可以看出与基于高度的histogram的区别，基于值的histogram为每一个值提供了一个bucket，从上面看到值1有19928行，值2有9999行，值3有9999行，值4有9912行，值5－75都分别只有1行，同样我们可以看出值1占的比例最大。
直方图的数据我们也看到了，但cbo怎么利用它来计算成本呢，做个10053看一下
alter session set events'10053 trace name context forever,level 1';
select object_name from test where object_id=1;
alter session set events'10053 trace name context off';
提取trace文件中的一段
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TEST Alias: TEST
#Rows: 49909 #Blks: 707 AvgRowLen: 95.00
Index Stats::
Index: IND_TEST_OBJECT_ID Col#: 4
LVLS: 1 #LB: 179 #DK: 5 LB/K: 35.00 DB/K: 175.00 CLUF: 879.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#4): OBJECT_ID(NUMBER)
AvgLen: 2.00 NDV: 75 Nulls: 0 Density: 1.0018e-05 Min: 1 Max: 75
Histogram: Freq #Bkts: 75 UncompBkts: 49909 EndPtVals: 75
Table: TEST Alias: TEST
Card: Original: 49909 Rounded: 19928 Computed: 19928.00 Non Adjusted: 19928.00
Access Path: TableScan
Cost: 158.56 Resp: 158.56 Degree: 0
Cost_io: 156.00 Cost_cpu: 18011198
Resp_io: 156.00 Resp_cpu: 18011198
Access Path: index (AllEqRange)
Index: IND_TEST_OBJECT_ID
resc_io: 423.00 resc_cpu: 11183699
ix_sel: 0.39929 ix_sel_with_filters: 0.39929
Cost: 424.59 Resp: 424.59 Degree: 1
Best:: AccessPath: TableScan
Cost: 158.56 Degree: 1 Resp: 158.56 Card: 19928.00 Bytes: 0
上面的selectivity就是通过histogram来计算的，如果不存在histogram的话那么selectivity=(1/number of distinct values),这里的selectivity=(值为1的行数/总行数）＝(19928/49909)=0.39929
再根据索引扫描成本计算公式
io_cost=resc_io= blevel+FF*leaf_blocks+FF*clustering_factor
＝1＋0.39929*179+0.39929*879
=1+71.47291+350.97591
=423
所以index访问成本大于table scan成本，最终cbo选择的路线为采用tablescan.
如果选择object_id=75呢？
alter session set events'10053 trace name context forever,level 1';
select object_name from test where object_id=75;
alter session set events'10053 trace name context off';
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TEST Alias: TEST
#Rows: 49909 #Blks: 707 AvgRowLen: 95.00
Index Stats::
Index: IND_TEST_OBJECT_ID Col#: 4
LVLS: 1 #LB: 179 #DK: 75 LB/K: 2.00 DB/K: 11.00 CLUF: 879.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#4): OBJECT_ID(NUMBER)
AvgLen: 2.00 NDV: 75 Nulls: 0 Density: 1.0018e-05 Min: 1 Max: 75
Histogram: Freq #Bkts: 75 UncompBkts: 49909 EndPtVals: 75
Table: TEST Alias: TEST
Card: Original: 49909 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50
Access Path: TableScan
Cost: 158.56 Resp: 158.56 Degree: 0
Cost_io: 156.00 Cost_cpu: 18011198
Resp_io: 156.00 Resp_cpu: 18011198
Access Path: index (AllEqRange)
Index: IND_TEST_OBJECT_ID
resc_io: 2.00 resc_cpu: 15503
ix_sel: 1.0018e-05 ix_sel_with_filters: 1.0018e-05
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: IND_TEST_OBJECT_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.50 Bytes: 0
对于non-popular value(数量比较少的值，比如在基于高度histogram里跨度不超过一个bucket或在基于值的histogram里行数比较少的值），oracle采取了不再去计算selectivity，而是采用density来做为
做为selectivity，从HIST_HEAD$里可以看到density，oracle采用density function(密度函数)来估计列的密度。
SQL> select OBJ#,COL#,BUCKET_CNT,ROW_CNT,SAMPLE_SIZE,MINIMUM,MAXIMUM,DISTCNT,DENSITY from sys.HIST_HEAD$ where obj#=51933 AND COL#=4;
OBJ# COL# BUCKET_CNT ROW_CNT SAMPLE_SIZE MINIMUM MAXIMUM DISTCNT DENSITY
---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
51933 4 49909 75 49909 1 75 75 .000010018
io_cost=resc_io= blevel+FF*leaf_blocks+FF*clustering_factor
＝1＋1.0018e-05*179+1.0018e-05*879
=1+0.001793222+0.008805822
=2(因为最少会读2个块)
所以最后cbo选择了index scan。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-629457/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-629457/
2477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
