Oracle 优化统计数据之直方图(histograms)

直方图是一种按数据出现的频率来进行分类存储的方法.在oracle中直方图是用来描述表中列数据的分布情况.每一个sql在被执行前都要经

直方图是一种按数据出现的频率来进行分类存储的方法.在Oracle中直方图是用来描述表中列数据的分布情况.每一个sql在被执行前都要经过优化这一步骤那么在优化器给出一个最优执行计划之优化器应该要知道sql语句中所引用的底层对象的详细信息.
 
直方图描述的对象包括列中不同值的数量和它们出现的频率.现在存储每一个不同值和它出现的频率是不可行的,特别是对于大表来说列中有上万个不同值,oracle使用直方图来存储关于列中数据分布的有用信息而且oracle的CBO使用直方图信息来计算出一个最优的执行计划.
 
CBO与直方图histograms
 从一个行源中评估返回行数所占的比例这就是选择率,选择率在CBO的查询优化中起着重要作用.选择率的取值范围是0到1之间.粗略的讲,如果满足谓词条件的只有少量的行记录那么CBO将更喜欢使用索引扫描,如果谓词条件要从表中获取大量数据那么CBO将更喜欢使用全表扫描.比如下面的查询获取deptno等于10的所有雇员信息如果返回少量的记录查询将会更倾向于使用索引扫描:
 select * from emp where deptno=10;

为了评估选择率(或者换句话说计算出最优执行计划),CBO会使用各种形式的统计信息,配置参数等.以表中列的角度来说,CBO会收集以下统计信息:
 列中不同值的数量也就是NDV
 列中的最小值/最大值
 列中null值的数量
 数据分布或直方图信息

在没有直方图时优化器使用基表中记录的列中不同值的数量,列中最小值/最大值和列中null值的数量来计算统计信息.使用这些信息优化器假设数据在列中的最小值和最大值之间是均匀分布的或者说列中每一个不同值的出现次数是相同的.
 下面举列说明.创建一个测试表t1它有10000行记录,有两个列,列all_distinct包含不同值的范围从1到10000.列skew对于前10行记录的值从1到10,余下的9990行记录都是10000.
 [oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 4 06:05:14 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1 as select rownum all_distinct,10000 skew from dual connect by level update t1 set skew=all_distinct where rownum commit;

Commit complete.

SQL> select skew,count(*) from t1 group by skew order by skew;

      SKEW  COUNT(*)
---------- ----------
        1          1
        2          1
        3          1
        4          1
        5          1
        6          1
        7          1
        8          1
        9          1
        10          1
    10000      9990

11 rows selected.

 
使用dbms_stata.gather_table_stats来收集统计信息是生成直方图是由参数method_opt来控制的method_opt参数的语法是由多个部分组成的.前两个部分是强制性的:
 FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
 FOR COLUMNS [size clause] column [size_clause] [,column...]

method_opt语法中的主要部分控制哪此列将收集列的统计信息(min,max,ndv,nulls).缺省是for all columns,它将会对表中所有的列(包括隐藏列)收集基本的列统计信息.
 
for all indexed columns将只对哪些包含索引的列进收集列统计信息.

for all hidden columns将只会对哪些虚拟列收集列统计信息.这意味着在对表收集统计时真实列是不会生成列统计信息的.这个值不能用于通常的统计信息收集.它只能用在当基表列的统计信息精确收集后在表中创建新的虚拟列.然后对新的虚拟列收集列统计信息时才使用它.
 
注意如果列不在统计信息收集列表中那么只会收集列的平均长度.

size用来指定直方图的桶数SIZE {integer | REPEAT | AUTO | SKEWONLY}
 auto:基于列的使用信息(sys.col_usage$)和是否存在数据倾斜来收集直方图
 integer:人为的指定创建直方图的桶数范围是1到254,如果size 1意味着不创建直方图
 repeat:只会对已经存在直方图的列重新生成直方图.如果是一个分区表,repeat会确保对在全局级别存在直方图的列重新生成直方图.这是不被推荐的设置的.当前直方图的桶数将会作为重新生成直方图所使用的桶数的最大值.比如,当前直方图的桶数是5,那么生成的直方图最大桶数就是5,说的直白点就是刷新现有直方图的列上的统计信息.
 skewonly:对任何数据分布出现倾斜列的自动创建直方图

现在来对表t1收集统计信息但不创建直方图
SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,density from user_tab_col_statistics where
  2  table_name='T1';

COLUMN_NAME                    NUM_DISTINCT    DENSITY
------------------------------ ------------ ----------
ALL_DISTINCT                          10000      .0001
SKEW                                    11 .090909091
 
如果没有直方图,列的density统计信息代表了它的选择率它是通过去时1/num_distinct=1/11=0.09090901来计算出来的.在有直方图的情况下,density的计算依赖于直方图的类型和oracle的版本.density值的范围是0到1之间.当查询使用这个列作谓词条件时优化器将会使用这个列的density统计信息来评估将要返回的行数.所以 cardinality(基数)=selectivity(选择率)* number of rows(表的行数)
 
下面来检查一下在谓词条件中列的数据分布存在倾斜而没有直方图的情况下其基数评估的情况:
SQL> explain plan for select * from t1 where skew=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013