ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle直方图

oracle直方图

Linux操作系统 作者:jijyoracle 时间:2016-04-08 10:55:13 0 删除 编辑

直方图原理总结:
 一)在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/30536096/viewspace-2077285/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2016-01-15

  • 博文量
    25
  • 访问量
    61218