ITPub博客

首页 > Linux操作系统 > Linux操作系统 > histogram与10053(zt)

histogram与10053(zt)

原创 Linux操作系统 作者:zhouwf0726 时间:2019-05-15 22:24:07 0 删除 编辑

histogramoraclecbo提供更精确的成本估计而设计的一种直方图数据。histogram能提供列的数据分布,每次分析表后列的分布信息将会被保存在统计表里面,分析时默认的histogram size75,意思就是采用75buckets来表示数据分布。

 

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'列有从176不同的76个值,bucket数量为75个,所以采取了基于高度的直方图。

 

可以看到1的值占据了129bucket2的值占据了3044bucket,3的值占据了45-59bucket,4的值占据了6074bucket,值576占据了75bucket。所以值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,从上面看到值119928行,值29999行,值39999行,值49912行,值575都分别只有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

10.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$里可以看到densityoracle采用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

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

上一篇: ORA-01975
请登录后发表评论 登录
全部评论

注册时间:2006-02-22

  • 博文量
    291
  • 访问量
    209671