MyException - 我的异常网
当前位置:我的异常网» 数据库 » 深入懂得Oracle优化器(1):倾斜列(skew)和histogram

深入懂得Oracle优化器(1):倾斜列(skew)和histograms

www.MyException.Cn  网友分享于:2013-02-20  浏览:3次
深入理解Oracle优化器(1):倾斜列(skew)和histograms
          ㈠ Histograms
          
          柱状图?直方图?其实这俩是一个概念,在这里Think直接用histograms来称呼
          histograms可以这么理解就是一个列上数值的大致分布的密度(density)和范围(range)
          通俗一些就是CBO用histgrams来更加准确的判断按照某个条件对每一列查询能返回多少记录
          
          histograms有两种类型
          
          ① 基于高度的histograms:每个范围包括相同数量的值,根据每个范围的终点的列值来判断数据的分布
          ② 基于数值的histograms:当列中不同的值的数量少于或等于histograms的buckets数量时,建立数值histograms
                                  这种histograms列中每个值都有对应的bucket,根据每个值对应的bucket的个数来判断数据的分布
          
          我们可以从视图dba_histograms/user_histograms,dba_tab_histograms查询
             
          ① 为什么需要histograms?
             
             我们对经常在WHERE子句中使用的且数据的分布十分不均匀的列使用histograms
             
             直方图究其根本实际上就是一个数据分布的图示,这个图示是为了在生成SQL执行计划的时候给Oracle的CBO更多的信息
             也就是说,当在where条件中的某些列可能由于列值的不同而希望CBO制定出不同的执行计划时,我们需要直方图
             
             那么,什么情况下我们不需要直方图呢?
             
             ⑴ 当此列不用于查询时,也就是这个字段永远不会出现在where条件中
             ⑵ 当此列无论给予什么比较值,我们都希望永远是一种执行计划时,比如,col1我们希望永远是用该列上的索引扫描
                  col2我们希望永远是全表扫描,这样的执行计划的制定,只要有表级别的统计信息就足够了
                  直方图信息的出现不但不会对制定正确的执行计划有帮助,甚至会出现奇怪的现象导致执行计划不稳定
             ⑶ 列中数据均匀分布,比如身份证号,QQ号,主键等
             ⑷ 列中数据唯一且只使用相等作为判定条件
             ⑸ 对这个列所有的判定条件都使用了绑定变量
          
          
          ② 如何搜集histograms
          
          只有我们的DBA才最知道哪些列上应该收集直方图,这实际上已经远远不仅仅是技术问题了
          而是一个业务问题,因此DBA应该去熟悉业务,DBA应该知道自己的应用的数据分布特点,应该知道哪些列会常被用在where条件中
          
             
          Ⅰ analyze  
     
          
             histograms事实上它描述的就是数据在存储桶的分布范围!
             如何生成histograms?可以通过对表做分析!
             analyze table table_name compute statistics:不仅分析了表和索引,而且分析了表上的所有列,并生成了列的histograms
             analyze table table_name compute statistics for table:仅仅产生表的statistics,不生成列的histograms
             analyze table table_name compute statistics for all indexed columns:分析了表,并仅对表上的索引列产生histograms
             analyze table table_name compute statistics for all columns:分析表,同时生成所有列的histograms
             对所有列都生成histograms是没有意义的,只有对where语句中用到的列生成histograms是有意义的
             默认列的histograms只有一个桶,可以对列分析时指定多个桶:
             analyze table table_name compute statistics for columns column_name size n;
          
          Ⅱ dbms_stats.gather_table_stats
          
             ⒈ 第一次收集统计信息时,设置method_opt=>FOR ALL COLUMNS SIZE 1,这意味删除所有列上的直方图
             ⒉ 在测试阶段或者在真实生产环境中,在调优SQL的过程中,DBA将会逐渐得知每个需要直方图信息的字段
                 在这些字段上人工收集统计信息,method_opt=>FOR COLUMNS SIZE AUTO [COLUMN_NAME]
                 如果你能够明确知道应该收集多少个bucket而手工指定SIZE值那更好。保留收集所有这些字段的脚本
                 以备数据库系统升级或者迁移时候使用
             ⒊ 在每次数据分布有所变化的时候,更新统计信息,使用method_opt=>FOR ALL COLUMNS SIZE REPEAT
                 这样只会收集已经存在了直方图信息的字段
             重复2,3步骤,直到系统稳定。这是一个可控的步骤,只有可控,才可能避免不可预知的错误
     
          
          
          
          ㈡ skew
          
          Oracle在界定skew时并不是用重复值来判断的
          Oracle认为的倾斜列是指在最大值和最小值之间分布不均匀,即使它是唯一的

          测试:

SQL> create table numa as select rownum a from histest;

Table created

Executed in 3.938 seconds

SQL> select count(*) from numa;

  COUNT(*)
----------
   1024384

Executed in 0.297 seconds

SQL> delete from numa where a between 9999 and 1024383;

1014382 rows deleted

Executed in 73.86 seconds

SQL> commit;

Commit complete

Executed in 0 seconds
SQL> select count(*) from numa;

  COUNT(*)
----------
     10001

Executed in 0.187 seconds

SQL> alter table NUMA
  2    add constraint UK_NUMA unique (A);

Table altered

Executed in 0.219 seconds

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'NUMA',method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed

Executed in 0.328 seconds

SQL> SELECT COUNT(*), column_name
  2    FROM dba_tab_histograms
  3   WHERE table_name = 'NUMA'
  4     AND column_name = 'A'
  5   GROUP BY column_name
  6   ORDER BY column_name DESC
  7  /

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------
       201 A

Executed in 0.078 seconds


1楼liz30022013-02-05 20:57
不错!!!

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有