ITPub博客

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

histogram与10053(zt)

原创 Linux操作系统 作者:sxzhanghl 时间:2009-06-07 13:26:55 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 ANDCOL#=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/730796/viewspace-605372/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-19

  • 博文量
    31
  • 访问量
    58151