Oracle的直方图

直方图

1、概述

     当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方图，直方图最大的桶数(Bucket)是254。收集直方图是一个很耗时的过程，如无必要，千万别去收集直方图。

    OLTP系统中没有必要千万不要去收集直方图，因为OLTP一般会用绑定变量，但11g之前一直有绑定变量窥探的问题，导致收集的直方图没有作用；

     对于SELECT列不要去收集直方图 要对WHERE列使用直方图  因为直方图是给CBO使用的

    请注意 在OLTP系统中如果没有必要千万不要收集图的统计，因为如果收集了可能遇到绑定变量窥探，在9i， 10g 几乎没有办法解决，在11g里面出了一个自适应游标，解决了绑定变量窥探，但是也是有风险。

2、如何查看直方图

直方图是根据统计信息而来的，所以要先收集统计信息

（1）收集统计信息

BEGIN     DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',                                    tabname          => 'TEST',                                    estimate_percent => 100,                                    method_opt       => 'for all columns size skewonly',                                    no_invalidate    => FALSE,                                    degree           => 1,                                    cascade          => TRUE);   END;   /

（2）查询统计信息含有直方图的信息

select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'SCOTT' and a.table_name = 'TEST'; 参数说明： NUM_ROWS 表示总行数 CARDINALITY 表示基数 SELECTIVITY表示选择性 选择性在10%以上都比较高了 HISTOGRAM表示直方图的类型： FREQUECNCY频率直方图、 当列中Distinct_keys 较少(小于254)，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于Distinct_Keys。 HEIGHT BALANCED 高度平衡直方图 当列中Distinct_keys大于254，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建高度平衡直方图。 NONE表示未收集直方图 NUM_BUCKETS 表示桶数

（3）直方图的效果

oracle 一般查询数据行数在5%以下希望走索引

生成了直方图之后 执行以下两个句子查看一下分别的执行计划对比看看

我们打开执行计划，只显示执行计划的结果， set autotrace traceonly; select * from test where owner='SYS'; 运行结果显示 --查询出3W行 ROWS估算3W行 估算正确 这里的总行数7w 所有走全表扫描 select * from test where owner='SCOTT'; --查询出13行 ROWS估算13行（低于5%） 估算正确 走索引 接下来我们删除直方图，在次查看执行计划

（4）删除直方图的影响

BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1', no_invalidate => FALSE, degree => 1, cascade => TRUE); END; / select * from test where owner='SYS'; --查询出3W行 ROWS估算2335行（低于5%）预估均匀分配走索引 估算错误 select * from test where owner='SCOTT'; --查询出13行 ROWS估算2335行（低于5%）预估均匀分配走索引 估算错误 如果是采样率estimate_percent100%，SQL条件不复杂的话，那么一般执行计划里估算的基数也是准确的； 如果一个表10个G 那么如果要采样率100% 会搞死人的 所以一般都是收集30%，这时候估算的基数不一定准确了，但是估算给CBO一般够了；

3、疑问使用直方图的场合

1、直方图到底应该什么时候收集直方图？

就查一下执行计划和实际查询行数进行比较 估算的基数ROWS是不是算错了。 

在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。 直方图的使用不受索引的限制，可以在表的任何列上构建直方图。 构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。 通常情况下在以下场合中建议使用直方图：

（1）、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。 （注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）

（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

2、只对有索引的列收集直方图也是错的！

3、直方图究竟是干嘛的？

告诉CBO 有没有收集直方图  这个列是不是均衡的 

1. 没收集直方图 ---CBO认为这个列是分布均匀的；

2. 收集过了 ---告诉CBO这个列数据有问题 分布不均衡  你别算错了 特别是频率直方图算的会很准

最终就是影响rows

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

转载于:http://blog.itpub.net/31485142/viewspace-2154489/