oracle bucket trace,Oracle 个人技巧 -- CURSOR_SHARING

一.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+FFleaf_blocks+FFclustering_factor

＝1＋0.39929179+0.39929879

=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+FFleaf_blocks+FFclustering_factor

＝1＋1.0018e-05179+1.0018e-05879

=1+0.001793222+0.008805822

=2(因为最少会读2个块)

所以最后cbo选择了index scan。