Oracle 直方图

Oracle 直方图解析
一、何谓直方图
直方图是一种统计学上的工具，并非Oracle 专有。通常用于对被管理对象的某个方面
的质量情况进行管理，通常情况下它会表现为一种几何图形表，这个图形表是根据从实际环
境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量
为底边，以频度为高度的一系列连接起来的矩形图，因此直方图在统计学上也称为质量分布
图。
二、Oracle 中直方图的作用
既然直方图是一种对被管理对象某一方面质量进行管理的描述工具，那么在Oracle 中
自然它也是对Oracle 中某个对象质量的描述工具，这个对象就是Oracle 中最重要的东西—
—“数据”。
在Oracle 中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同
值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数
据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还
是执行全表扫描的决策。当where 子句的值具有不成比例数量的数值时，将出现这种情况，
使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有
一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL 语句执
行成本最低从而提升性能。
三、Oracle 中使用直方图的场合
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器
就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图
的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例
如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少
满足查询所需的I/O 数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合
适，或何时应该根据WHERE 子句中的值返回表中80％的记录。
通常情况下在以下场合中建议使用直方图：
（1）、当Where 子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以
至于WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优
化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很
常见，许多DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）
（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我
们有一个五项的表联接，其结果集只有10 行。Oracle 将会以一种使第一个联接的结果集
（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将
会运行得更快。为了使中间结果最小化，优化器尝试在SQL 执行的分析阶段评估每个结果
集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器
对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因
此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。
四、如何使用直方图
（1）、创建直方图
通过使用早先的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 => user,
tabname=>''，
estimate_percent =>dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
cascade=>true,
degree=> 2);
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 => USER,
tabname=>''，
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade=>true，
degree => 2
);
end;
重新分析统计数据时，使用repeat 选项，重新分析任务所消耗的资源就会少一些。使
用repeat 选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。示例如下：
BEGIN
dbms_stats.gather_ table _stats(
ownname => USER,
tabname=>''，
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade=>true，
degree => 2
);
END;
（2）、创建直方图的考虑因素
如果想为某一列创建直方图，示例如下：
begin
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 之间。
（3）、删除直方图信息
在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');
五、Oracle 直方图的种类
Oracle 利用直方图来提高非均匀数据分布的选择率和技术的计算精度。但是实际上
Oracle 会采用两种不同的策略来生成直方图：其中一种是针对包含很少不同值的数据集；
另一种是针对包含很多不同的数据集。Oracle 会针对第一种情况生成频率直方图，针对第
二种情况生成高度均衡直方图。通常情况下当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)
数。
（1）、频率直方图
Oracle 中的频率直方图是按照累积某一列值的出现次数来生成数据分布描述的。我们
举一个例子如下所示：我们创建一张表表t1，其中包含一个skew 列，我们规定如下数据填
充策略：skew=1 出现一次，skew=2 出现两次......skew=80 出现80 次。因此代码如下：
create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= 80
and v2.id <= 80
and v2.id <= v1.id
order by
v2.id,v1.id;
收集生成frequency histograms:
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 120'
);
end;
/
收集后查询user_table_histograms：
select endpoint_number, endpoint_value
from user_tab_histograms
where column_name = 'SKEW' and table_name = 'T1'
order by endpoint_number;
结果如下：
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 1
3 2
6 3
10 4
15 5
21 6
..................
..................
..................
3081 78
3160 79
3240 80
我们可以看出频率直方图对t1 里每一个distinct 都保留了一行（所以才说frequency
histograms 是只能用在distinct key <255 的表上，因为histograms 的最大bucket 数是
254）从这个输出里面我们可以看到等于1 的值有一个，等于1 和2 的值有3 个（因此等于
2 的值有2 个），等于1/2/3 的值有6 个。。。。。。因此从这个角度来说，我们常常把frequency
histograms 称为累计的frequency histograms。
在Oracle 10GR2 之前用dbms_stats 来收集统计信息的时候，你会发现如果你使用的
bucket 是80，你不会得到一个frequency histograms 的统计信息，而会得到一个height
balanced histograms 的统计信息，因此只能使用analyze 命令的for all columns size 80
来得到frequency histograms 的统计信息。经过多次实验你会发现对于表t1，如果你想得
到frequency histograms，你的bucket 至少得设置为107，这个问题在Oracle10GR2 后得
到了修正，但是因为histograms 的最大bucket 为254 个，所以对于distinct key 是大于
180 的话，用dbms_stats 还是永远得不到frequency histograms 的，这时候必须使用
analyze。或者自己来生成统计信息并且在生成后更新数据字典。
接下来我们看一下频率直方图对于查询语句成本的影响。在讨论这个话题前我们要先明
晰一个概念——查询基数，所谓查询基数可以简单的理解为一个查询语句将要预计返回的查
询结果的行数，计算基数的基本公式为：总记录数*选择率，对于选择率的计算比较复杂，
不同情况下会采用不同的计算方法，但通常都会参照表的num_distinct 值，在有些使用绑
定变量的情况下甚至直接用1/num_distinct 值来作为选择率。在得到基数后优化器会使用
基数来生成查询成本，因此基数对于查询成本来说非常重要。因此按照对于SKEW 列的不同
过滤条件会生成不同得基数如下所示：
select * from t1 where SKEW=40;该语句基数会得到40，因为SKEW=40 的值共出现了
40 次；
select * from t1 where SKEW between 1 and 2; 该语句基数会得到3, 因为SKEW=1
和2 的值共出现了3 次；
select * from t1 where SKEW=40.5; 该语句基数会得到1，因为SKEW=40.5 的值不存
在。CBO 在我们认为是0 行的地方统一的看作是1 行，实际上除非你的条件里面加入1=0 这
样的条件，否则CBO 一般是不允许cardinality 为0 的。
select * from t1 where SKEW between 20.5 and 24.5;对于t1 表该语句将会得到与
select * from t1 where SKEW between 21 and 24;相同的基数。
select * from t1 where skew=:skew 得到的基数将会是41。对于带有绑定变量的等
值查询，选择率为1/num_distinct。
select * from t1 where skew>=:skew 得到的基数将会是162。对于带有绑定变量的
>=、>、<=、< 选择率固定为5%。
select * from t1 where skew between :skew1 and :skew2 得到的基数是8。对于带
有绑定变量的范围查找，选择率为5%*5%=0.25%；
总结如下：