oracle直方图

直方图原理总结：
 一）在oracle中使用直方图来表示数据的分布质量。它会按照某一列不同值出现的数量的多少，以及出现频率的高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正
确的选择（是使用索引还是全表扫描）。

 二）如果where子句的过滤谓词的列上有一个合理的正确的直方图，它将会对优化器做出正确的选择发挥巨大的作用，使得sql语句执行成本最低从而提升性能。在获得准确的直方图信息
后，基于成本的优化器，就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。

 三）直方图的使用不受索引的限制，可以在表的任何列上构建直方图。构建直方图最主要的原因就是，帮助优化器在表中数据严重倾斜时做出更好的抉择。例如：一到两个值（status=0
和status=1，其中=0有100条数据，=1有1000000条数据，只有这两个值）就构成了表中的大部分数据（数据倾斜），相关查询就可能无法帮助减少满足查询所需的I/O数量（如查询
status=1）。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时根据where子句中的值返回表中的80%的记录。

 四）通常在以下场合建议创建和使用直方图：
 1）当where子句引用了列值的分布情况存在明显偏差的列时：当这中偏差相当明显时，以至于where子句中的值将会使优化器选择不同的执行计划。这时，应该使用直方图来帮助优化
 器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多DBA会在偏差列上创建柱状图，即使没有任何查询引用该列）
 
 2）当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如：假设有个五项的表连接，其结果集只有10行记录。oracle将会以一种第一个连接的结果集（集合基数）尽
 可能小的方式将表连接起来。通过在中间结果集中携带更少的负载，查询将会运行的更快。为了使中间结果集最小化，优化器尝试在sql执行的分析阶段评估每个结果集的集合基数。
 在偏差的列上拥有直方图将会极大的帮助优化器做出正确的决策。如优化器对中间结果集的大小做出不正确的判断，它可能会选择一种未达到最优化的表连接方法，因此向该列添加直
 方图经常会向优化器提供使用最佳连接方法所需的信息。
 
 五）直方图的分类：可分为等频直方图和等高直方图
 等频直方图：针对包含很少不同值的数据集，就是数据分布很均匀。
 等高直方图：针对包含很多不同值的数据集。 数据分布不均匀 ，由于列中数据很多，这时数据比较密集，不利于分析和评估，这时直方图需要均衡化默认的，如果一个倾斜列上的唯
 一值超过了254个，那么oracle会对此列创建等高直方图，否则建立等频直方图。
 直方图信息的准确性由两个数值决定，一个是bucket的个数，一个是num_distinct的个数。一般来说，bucket的数量越多，关于列数据分布的信息就越准确，但统计直方图花费的时间
 就越多，oracle中bucket的最大为254个，默认是75个。而sql server中默认是200个。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而
 当BUCTET > 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是
 HEIGHT BALANCED（高度平衡）直方图。在Oracle 10GR2之前如果使用dbms_stats包来创建直方图，那么如果指定需要创建的直方图的桶的数目与表的NUM_DISTINCT值相等，那么几乎
 无法创建出一个FREQUENCY（频率）直方图，此时为了得到频率直方图只能使用analyze命令的“for all columns size 表的NUM_DISTINCT值”，这在某种程度上来说是一个退步，但这个
 问题在Oracle 10GR2后被修正。但是如果列中有180 - 200个不同值时，还是无法创建FREQUENCY（频率）直方图.此时需要手工建立直方图,并写入数据字典方能完成FREQUENCY（频率）
 直方图的创建。对于含有较少的不同值而且数据分布又非常不均匀的数据集来说，创建FREQUENCY（频率）直方图将会更加合适，因为它不存在会将低频出现的记录存入高频桶中的情况
 ，而HEIGHT BALANCED（高度平衡）直方图在存储桶(bucket)数分配不合理时就可能会出现这种情况。因此一定要在创建直方图前确定使用何种直方图，并且要合理估计存储桶(bucket)
 个数。
 
 六）如何创建直方图：
 通过使用早先的analyze命令和最新的dbms_stats工具包都可以创建直方图。Oracle推荐使用后者来创建直方图，而且直方图的创建不受任何条件限制，可以在一张表上的任何你想创建
 直方图的列上创建直方图。我们这里主要介绍如何通过dbms_stats包来创建直方图。
 Oracle 通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在 method_opt 子句中有三个相关选项，即 skewonly、repeat 和 auto。
 “skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL
 优化器决定是使用索引还是全表扫描访问。示例如下：
 begin
 dbms_stats. gather_table_stats (
 ownname=> '',
 tabname=>''，
 estimate_percent =>dbms_stats.auto_sample_size,
 method_opt=> 'for all columns size skewonly',
 cascade=>true,
 degree=> 7);
 end;
 其中degree指定了并行度视主机的CPU个数而定，estimate_percent指定了采样比率，此处使用了auto目的是让oracle来决定采样收集的比率，绘制直方图时会根据采样的数据分析结果
 来绘制，当然也可以人为指定采样比率。如：estimate_percent=>20指定采样比率为20%，cascade=>true指定收集相关表的索引的统计信息，该参数默认为false，因此使用dbms_stats
 收集统计信息是默认不收集表的索引信息的。
 
 在对表实施监视 (alter table xxx monitoring;) 时使用 auto 选项，它基于数据的分布以及应用程序访问列的方式（例如由监视所确定的列上的负载）来创建直方图。示例如下：

 begin
 dbms_stats.gather_ table _stats(
 ownname => '',
 tabname=>''，
 estimate_percent => dbms_stats.auto_sample_size,
 method_opt => 'for all columns size auto',
 cascade=>true，
 degree => 7
 );
 end;
 重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。示例如下：
 dbms_stats.gather_ table _stats(
 ownname => '',
 tabname=>''，
 estimate_percent => dbms_stats.auto_sample_size,
 method_opt => 'for all columns size repeat',
 cascade=>true，
 degree => 7
 );
 end;
 
 七）创建直方图的考虑因素：
 如果想为某一列创建直方图，示例如下：
 dbms_stats.gather_ table _stats(
 ownname => '',
 tabname=>''，
 estimate_percent => dbms_stats.auto_sample_size,
 method_opt => 'for columns size 10 列名',
 cascade=>true，
 degree => 7
 );
 end;

 其中size 10指定的是直方图所需的存储桶(bucket)数，所谓存储桶可以理解为存储数据的容器，这个容器会按照数据的分布将数据尽量平均到各个桶里，如一张表如果有6000条记
 录，那么每个桶中平均就会有600条记录，但这只是一个平均数，每个桶中的记录数并不会相等，它会将高频出现记录集中在某一些桶中，低频记录会存放在少量桶中，因此如果存
 储桶(bucket)数合适的增加就会减少高频记录使用的桶数，统计结果也会更加准确（可以避免被迫将低频记录存入高频桶中，影响优化器生成准确的执行计划）。所以我们最后得
 到的直方图信息的准确性就由两个数值决定，一个是BUCTET的个数，一个NUM_DISTINCT的个数。所以创建直方图时首先要正确地估计存储桶(bucket)数。默认情况时，Oracle的直
 方图会产生75个存储桶。可以把SIZE的值指定在1~254之间。

 八）删除直方图信息：
 在oracle中要删除直方图信息就是设置bucket的数据为1，如下：

 Analyze table 表 compute statistics for table for columns id size 1；

 exec dbms_stats.gather_table_stats('用户', '表',cascade=>false, method_opt=>'for columns 列 size 1');
 但这却得再次收集表的统计信息，十分不合理，11g有如下方法可以直接删除直方图信息
 dbms_stats.delete_column_stats(
 ownname => '',
 tabname => '',
 colname => '',
 col_stat_type => 'HISTOGRAM')
 相关测试实验可参考：http://blog.sina.com.cn/s/blog_63972ba20100x3mh.html
 
 九）直方图与绑定变量
 主要测试直方图不适合在sql语句使用绑定变量场合下使用：http://blog.163.com/scott_guo/blog/static/18102608320122551225491/
 
 十）查看表是否有直方图信息
 User_Tab_Histograms(普通表)、User_Part_Histograms(表分区)中是否有相关表的记录
 查询索引的选择率：索引的选择率=distinct_keys/num_rows*100%。如果此值比较小，则说明数据分布不均匀。
 select a.owner,
       a.index_name,
       a.index_type,
       partitioned,
       b.num_rows,--表示表中有多少条记录
       b.distinct_keys,--表示索引的列上有多少个不同的值
       b.num_rows / b.distinct_keys avg_row_per_key,
       b.distinct_keys / b.num_rows SELECTIVITY,
       b.last_analyzed,
       b.stale_stats
 from dba_indexes a, dba_ind_statistics b
  where a.owner = b.owner
  and a.index_name = b.index_name
  --and a.table_name='Q_PARTITION_RANGE'
  and a.index_name = 'INDEX_LR_QOSSTAFFID_RANGE'
  
 查询列上有没有直方图：
 select owner,table_name,column_name,num_distinct,histogram,num_buckets from dba_tab_col_statistics
  where table_name='Q_PARTITION_RANGE' and column_name='LR_STAFFID'
 num_buckets为1表示此列没有直方图
 有时，使用dbms_stats收集统计信息后反而不会走索引或者说走我们期望走的索引，此时用analyze收集确能走索引；但是使用analyze收集统计信息后，索引对应的列上可能没有直
 方图信息，此时可改用dbms_stats来收集一下，然后查询该列是否有直方图信息，如果有了再次执行语句，看是否走索引，不走索引可以通过dbms_stats删除改列的直方图信息：
 EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'',TABNAME=>'',ESTIMATE_PERCENT=>100,DEGREE=>16,method_opt=>'for columns size 1 LR_STAFFID',CASCADE=>TRUE);
 然后验证该列是否有直方图信息，并执行sql查看是否走索引。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23502881/viewspace-774463/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23502881/viewspace-774463/