histogram与10053(zt)

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                      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/756652/viewspace-470124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-470124/