一、何谓直方图

直方图是一种统计学上的工具，并非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（高度平衡）直方图。在ORACLE10GR2之前如果使用dbms_stats 包来创建直方图，那么如果指定需要创建的直方图的桶的数目与表的NUM_DISTINCT 值相等，那么几乎无法创建出一个FREQUENCY（频率）直方图，此时为了得到频率直方图只能使用analyze 命令的“for all columns size 表的NUM_DISTINCT值”，这在某种程度上来说是一个退步，但这个问题在ORACLE10GR2 后被修正。但是如果列中有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 都保留了一行（所以才说frequencyhistograms 是只能用在distinct key <255 的表上，因为histograms 的最大bucket 数是254）从这个输出里面我们可以看到等于1 的值有一个，等于1 和2 的值有3 个（因此等于2 的值有2 个），等于1/2/3 的值有6 个。。。。。。因此从这个角度来说，我们常常把frequencyhistograms 称为累计的frequency histograms。

在ORACLE10GR2 之前用dbms_stats 来收集统计信息的时候，你会发现如果你使用的bucket 是80，你不会得到一个frequency histograms 的统计信息，而会得到一个heightbalanced 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%；

总结如下：

基本查询基数会采用作为过滤条件列的不同值数量作为基数；

对于绑定变量如果对于等值比较如： column=:bind 的选择率会直接使用1/num_distinct 值，然后在用选择率*数据行数得到基数。对于范围比较的绑定变量，选择率会固定为5%，如使用column between :bind1 and :bind2 那么选择率将等于5%*5%=0.25%；

如果生成直方图的关键列值作了修改发生了变化，那么要重新生成直方图，否则得到的数据描述将会是过时的；

对于频率直方图我们可以看出，对于走出最大值/最小值范围的区间谓词CBO 会得到更好的结果；

2、高度均衡直方图

当我们给出的bucket 数目不够大的时候，ORACLE会以height balanced 的方式记录histograms，也就是按照buckets 的值把所有的数据平分，如果bucket 是50，就把所有的数据平分为50 等份，再告诉我们处于每个边界的值。如下所示：

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

create index t1_i1 on t1(skew);

然后我们以bucket 为75 建立histograms 信息。

begin

dbms_stats.gather_table_stats(

user,

't1',

cascade => true,

estimate_percent => null,

method_opt => 'for all columns size 75'

);

end;

查询histograms 的信息：

ENDPOINT_NUMBER ENDPOINT_VALUE

--------------- --------------

0 1

1 9

2 13

3 16

4 19

5 21

..................

..................

..................

62 73

64 74

65 75

67 76

69 77

71 78

73 79

75 80

59 rows selected.

发现查询user_tab_histograms 只有59 行输出，在仔细看看发现ORACLE确实是产生了75个bucket，因为ENDPOINT_NUMBER 的最大值是75，只不过在记录统计信息ORACLE进行了压缩，省略了一些bucket 的输出。上面的输出其实可以展开成如下形式：

ENDPOINT_NUMBER ENDPOINT_VALUE

60 72

61 73

62 73

63 74

64 74

65 75

66 76

67 76

68 77

69 77

70 78

71 78

72 79

73 79

74 80

75 80

在进一步解释其他关于histograms 的信息之前，我们要先介绍一个数据density 的概念。Density 的出现就是为了分析高频率出现的值的影响。没有histograms 信息的时候，DENSITY 永远等于1/NUM_DISTINCT，但是当我们统计了histograms 之后，DENSITY 就会发生改变。

2.1 DENSITY的计算方法如下

如果想计算一张表的DENSITY，首先运行下列语句收集信息：

select min(minbkt),

maxbkt,

substrb(dump(min(val), 16, 0, 32), 1, 120) minval,

substrb(dump(max(val), 16, 0, 32), 1, 120) maxval,

sum(rep) sumrep,

sum(repsq) sumrepsq,

max(rep) maxrep,

count(*) bktndv,

sum(case when rep = 1 then 1 else 0 end) unqrep

from (select val,

min(bkt) minbkt,

max(bkt) maxbkt,

count(val) rep,

count(val) * count(val) repsq

from (select

/*+

cursor_sharing_exact

use_weak_name_resl -- 10g hint

dynamic_sampling(0)

no_monitoring

*/

目标列val, ntile(10) over(order by 目标列) bkt

from 目标表名t

where 目标列is not null)

group by val)

group by maxbkt

order by maxbkt;

通过上面收集的信息计算densitiy：

（1）、基本公式

(sum(sumrepsq)-sum(maxrep(i)*maxrep(i))/(sum(sumrep)*(sum(sumrep)-sum(maxrep(i))))

注：i 表示只有某些特定的行从查询结果中选出来计算maxrep 值，这些行必须满足条件：maxbkt>min(minbkt)+1或者min(val)=max(val)

（2）、简化公式（在没有高频值出现的情况下使用）

sum(sumrepsq)/(sum(sumrep)*sum(sumrep))

2.2 使用高度均衡直方图成本计算方法

在知道了densitiy 的概念后我们再回头关注我们的表t1 的histograms 信息，我们尤其关注一下ENDPOINT_VALUE=75 的情况，之所以关注75 是因为我们发现75 周围的数值（比如74）都在输出当中出现了多次，只有75 只出现了一次，我们自己知道75 其实出现的次数应该是不比74 在表里出现的次数少的，但不巧的是75 在统计Histograms 的时候处在了一个特殊的位置，如下图所示 ：

这种不巧会导致ORACLE使用不同的机制来计算skew=75 的基数，在ORACLE看来当把histograms 数据完全展开成上面查询结果的形式后，凡是在histograms 信息里面出现2次或2 次以上的数据都是常见的数据，叫做popular 的数据，只出现一次或没出现的数据都是un-popular 的数据。

对于popular 的数据，对于=的条件，计算基数的公式是：

基数=总行数*（出现次数/bucket 总数）

其中“出现次数/bucket 总数”也就是选择率，比如我们上面的74 的基数= 3240 * （2/75) = 86.4，和我们下面的实验相符：

SQL> select count(*) from t1 where skew=74;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=86 Bytes=258)

注：

如果查询select count(*) from t1 where skew=80 发现估算出来的基数是65。

SQL> select * from t1 where skew=80;

Execution Plan

----------------------------------------------------------

Plan hash value: 838529891

----------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

----------------------------------------------------------

| 0 | SELECT STATEMENT | | 65 | 26260 | 32 |

|* 1 | TABLE ACCESS FULL| T1 | 65 | 26260 | 32 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("SKEW"=80)

由于80 是处在最后一个桶子上，但是ORACLE并不知道这个最后桶子的上界值是否还是80。ORACLE给出了50%的可能性。此时的选择率为：3240*((1+0.5)/75)=65

对于un-popular 的数据，对于=的条件，计算基数的公式是：

总行数*DENSITY，这里就是3240*0.013885925=44.99

SQL> select count(*) from t1 where skew=75;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=135)

这个计算方式在10.2.0.4 后又改变了。

以下是我的测试：

SQL> conn ysp/ysp

Connected.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------------------

ORACLEDatabase 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio

NLSRTL Version 10.2.0.4.0 – Production

SQL> select density from user_tab_columns where table_name='T1' AND COLUMN_NAME='SKEW';

DENSITY

----------

.013973812

SQL> SELECT 3240*.013973812 FROM DUAL;

3240*.013973812

---------------

45.2751509

SQL> set autot traceonly exp

SQL> select * from t1 where skew=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2755060842

---------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

---------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 29 | 11716 | 30 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 29 | 11716 | 30 |

|* 2 | INDEX RANGE SCAN | T1_I1 | 29 | | 1 |

---------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("SKEW"=1)

SQL> alter session set optimizer_features_enable ='10.1.0';

Session altered.

SQL> select * from t1 where skew=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 838529891

----------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

----------------------------------------------------------

| 0 | SELECT STATEMENT | | 45 | 18180 | 32 |

|* 1 | TABLE ACCESS FULL| T1 | 45 | 18180 | 32 |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("SKEW"=1)

􀁺 range scan 的公式是：

如果不跨整个的bucket:

选择率= (required range) / (high value - low value) + 2 * density

基数=每个bucket 的行数*选择率

如果跨整个的bucket：

选择率= (required range) / (high value - low value) + 2 * density + 整个的bucket

的个数

基数=每个bucket 的行数*选择率

我们一个实例来说明：

create table t1

as

/*

with generator as (

select --+ materialize

rownum n1

from all_objects

where rownum <= 5000

)

*/

select

/*+ ordered use_nl(v2) */

3000 + trunc(2000 * dbms_random.normal) n1,

lpad(rownum,10) small_vc,

rpad('x',100) padding

from

generator v1,

generator v2

where

rownum <= 10000;

insert into t1

select

500 * (1 + trunc((rownum-1)/500)),

lpad(rownum,10),

rpad('x',100)

from

t1;

commit;

begin

dbms_stats.gather_table_stats(

user,

't1',

cascade => true,

estimate_percent => null,

method_opt => 'for all columns size 250'

);

end;

/

histograms 的信息大致如下：

.......

ENDPOINT_NUMBER ENDPOINT_VALUE

--------------- --------------

8 -120

9 17

10 117

11 251

12 357

13 450

19 500

20 520

21 598

22 670

首先如果不跨整个的bucket:

select

small_vc

from t1

where n1 between 100 and 200;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=53 Card=63 Bytes=945)

1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=63 Bytes=945)

按照公式计算：

选择率= (required range) / (high value - low value) + 2 * density =

(200–117)/(251-117) + (117-100)/(117-17) + 2 * 0.000177746 =

0.619403 + 0.17 + .000355486 =

0.789047508

基数= 选择率* number of rows IN A BUCKET =

0.789047508 * 80 = 63.1238

如果跨多个bucket：

select

small_vc

from t1

where n1 between 400 and 600;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=53 Card=685 Bytes=10275)

1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=685 Bytes=10275)

按照公式计算：

选择率= (required range) / (high value - low value) + 2 * density + 整个的bucket

的个数

该查询中共垮了从13-21 的8 个bucket，因此计算结果如下：

(450 - 400) / (450 - 357) + (600 - 598) / (670 - 598) + 2 * 0.000177746 + 8 =

50 / 93 + 2 / 72 + 0.000355486 +8 =

0.537634 + 0.0277778 + 0.000355486 + 8=8.565768

基数= 基数* number of rows IN A BUCKET = 8.565867 * 80 = 685.3

六、手工生成频率直方图

上面我们曾经讲过，如果一张表中的不同值超过了180，那么是无法生成频率直方图的，因此在这种情况下我们要手动生成频率直方图，手动生成频率直方图的代码如下：

declare

m_statrec dbms_stats.statrec;

m_val_array dbms_stats.numarray;

-- m_val_array dbms_stats.datearray;

-- m_val_array dbms_stats.chararray; -- 32 byte char max

-- m_val_array dbms_stats.rawarray; -- 32 byte raw max

m_distcnt number;

m_density number;

m_nullcnt number;

m_avgclen number;

begin

dbms_stats.get_column_stats(ownname =>目标用户,

tabname => '目标表',

colname => '目标列',

distcnt => m_distcnt,

density => m_density,

nullcnt => m_nullcnt,

srec => m_statrec,

avgclen => m_avgclen);

--

-- Load column information into the two critical arrays

--

select 目标列, count(*) bulk collect

into m_val_array, m_statrec.bkvals

from 目标表

group by 目标列

order by 目标列;

m_statrec.epc := m_val_array.count;

--

-- Should terminate here if the count exceeds 254

--

dbms_stats.prepare_column_values(srec => m_statrec,

numvals => m_val_array);

select 1 / (2 * count(*)) into m_density from 目标表;

dbms_stats.set_column_stats(ownname =>目标用户,

tabname => '目标表',

colname => '目标列',

distcnt => m_distcnt,

density => m_density,

nullcnt => m_nullcnt,

srec => m_statrec,

avgclen => m_avgclen);

end;

该方法通过dbms_stats.get_column_stats 包来收集需要创建直方图的目标表和目标列信息，然后通过1 / (2 * num_rows) 来生成频率直方图的density，最后通过dbms_stats.set_column_stats 将收集的信息和计算结果更新到存放统计信息数据字典中。在频率直方图中有一些特殊的需要注意之处，在频率直方图中density 始终等于1 / (2* num_rows)，对于查询返回的行数来说density 非常关键。如果直方图中存在一个特定值一共有X 行，但是如果X 小于density* user_tables.num_rows-user_tab_columns.num_nulls 那么优化器将采用后者来取代X。

七、直方图与绑定变量

我们知道当我们拥有了histograms 的统计信息之后我们就可以使用这些信息计算我们的选择率和基数。但是如果我们使用了绑定变量的时候，情况总会有所改变。首先，在ORACLE9i 里面新引入了bind variable peeking 的功能，这个功能我们前面讲过，是一个带绑定变量的SQL 第一次parse 的时候，让CBO 可以根据绑定的具体的值来决定所要使用的执行计划，而以后如果遇到同样的SQL，即使绑定变量的值不一样，也不会在peek绑定变量的值，而是使用已经生成的计划。这里的一个潜在的问题就是如果我们有了histograms信息，而且我们的数据分布是一小部分数据的分布和其他部分的分布相差很远，那么当我们在做bind variable peeking，如果很不幸运的peek 到了那一小部分的数据，就会导致以后所有的同样的SQL 都使用了不恰当的执行计划。当然这个bind variable peeking 有时候也有意外，那就是如果我们存在shared pool里的执行计划信息或其他相关的信息由于某种原因失效了或者被age out of shared pool，那当我们再次运行这个SQL 的时候，就会重新peek 绑定变量的值，从而重新生成计划。关于执行计划信息或其他相关的信息的失效或age out，可以通过v$sql 的reloads 和invalidations 字段获得。

和绑定变量有关的另一个就是参数cursor_sharing，cursor_sharing 这个参数有三个取值：FORCE、EXACT、SIMILAR。有时候，很可能是在OLTP 的系统中，为了最大限度的减少SQL PARSE 的消耗，让类似的SQL 可以尽可能的重用，我们会考虑设置cursor_sharing 为force。当cursor_sharing被设置为force 的时候，优化器会用系统指定的绑定变量来替代SQL 里面所有的literal constants，然后以此为基础判断我们的shared pool 里面是不是有可以重用的cursor。按照我们上面的讨论，设置cursor_sharing 为force 对histograms 影响最大的，因为系统指定的绑定变量替换后很可能与histograms 收集的数据分布不符。这个问题可以有两个解决办法,一是在我们认为影响会很到的SQL 里面加上hint /*+cursor_sharing_exact */，这回告诉CBO 对于这个SQL 采用cursor_sharing=exact 的策略。

另一个解决方法是设置cursor_sharing=similar，按照ORACLE文档的说法，设置cursor_sharing 为similar 也会首先把SQL 里的literals 替换为绑定变量，并且也会在第一次分析SQL 的时候做bind variable peeking，但是当以后重新运行类似的SQL 的时候，CBO 会查看如果发现新的绑定变量会影响到执行计划（当然，之所以会产生不同的执行计划往往是因为存在histograms），就会重新生成执行计划。经过一些实验，我们可以发现，当设置cursor_sharing=similar 的时候，如果我们的条件是range scan或等于的条件，并且条件涉及的列上有histograms 信息的时候，CBO 会在分析SQL 的时候对绑定变量做检查，如果发现新的绑定变量有可能影响SQL 的执行计划，则会重新评估并生成新的计划。

但是往往我们在优化系统的一个方面的时候会导致其他方面的问题，cursor_sharing=similar 就是一个很典型的例子，当我们这样的设置的时候，首先优化器的压力会变大，因为CBO 要做很多的重新优化。更严重的问题在于cursor_sharing=similar会导致同样的SQL（除了绑定变量的值不一样之外）在library cache 里面拥有很多不同的执行计划，因为我们知道一个SQL 下面的所有执行计划都是被一个latch 保护的，所以cursor_sharing=similar 会导致更严重的latch 争用。因此当我们使用cursor_sharing=similar 的时候，除非必要，无需统计histograms 信息，因为我们要保证我们为了解决一个问题不会导致其他的更严重的问题。

八、何时使用直方图

当系统中的某些表存在高度不均匀的数据分布时，使用柱状图能够产生更好的选择性评估，从而产生更加优化的执行计划。柱状图提供一种有效和简捷的方法来呈现数据的分布情况。下面通过一个具体的例子解释柱状图的使用。

SQL> create table tab (a number, b number);

Table created.

SQL> begin

       for i in 1..10000 loop

         insert into tab values (i, i);

       end loop;

       commit;

     end;

     /

PL/SQL procedure successfully completed.

SQL> update tab set b=5 where b between 6 and 9995;

9990 rows updated.

SQL> commit;

Commit complete.

这样在tab表中，b列有10个不同的值，其中等于的值有9991个。在创建索引之前，无论是查询b=3或者是b=5，都只能是走全表扫描（FULL TABLE SCAN），因为没有别的可以使用的访问路径。下面我们在b列上创建一个索引。

SQL> create index ix_tab_b on tab(b);

Index created.

SQL> select index_name, table_name, column_name, column_position, column_length

     from user_ind_columns

     where table_name='TAB';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME          COLUMN_POSITION COLUMN_LENGTH

------------------------------ ------------------------------ -------------------- --------------- -------------

IX_TAB_B                       TAB                            B                                  1            22

现在我们分别来看看下面的查询。

SQL> select * from tab where b=3;

1 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 439197569

------------------------------------------------

| Id  | Operation                   | Name     |

------------------------------------------------

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |

|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |

------------------------------------------------

Statistics

----------------------------------------------------------

        178  recursive calls

          0  db block gets

         30  consistent gets

          5  physical reads

        116  redo size

        462  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> select * from tab where b=5;

9991 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 439197569

------------------------------------------------

| Id  | Operation                   | Name     |

------------------------------------------------

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |

|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |

------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       1370  consistent gets

         16  physical reads

          0  redo size

     206729  bytes sent via SQL*Net to client

       7711  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       9991  rows processed

可以看出这里走的都是基于RBO的INDEX RANGE SCAN。接下来，我们使用计算统计对表进行分析。

SQL> analyze table tab compute statistics;

Table analyzed.

SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len

from dba_tables

where table_name = 'TAB';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN

---------- ---------- ------------ ---------- ---------- -----------

     10000         20            4       2080          0          10

SQL> select num_distinct, low_value, high_value, density, num_buckets, last_analyzed, sample_size from dba_tab_columns where table_name = 'TAB';

NUM_DISTINCT LOW_VALUE            HIGH_VALUE              DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE

------------ -------------------- -------------------- ---------- ----------- --------- -----------

       10000 C102                 C302                      .0001           1 21-DEC-08       10000

          10 C102                 C302                         .1           1 21-DEC-08       10000

SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_tab_histograms where table_name = 'TAB';

TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ -------------------- --------------- --------------

TAB                            A                                  0              1

TAB                            A                                  1          10000

TAB                            B                                  0              1

TAB                            B                                  1          10000

再来执行上面的两个查询，观察其执行计划，发现两个查询仍然走的都是INDEX RANGE SCAN，只不过这时的执行计划是基于CBO的。

现在我们创建tab表b列的柱状图统计信息，使得优化器能够知道该列每个值的具体分布情况。

SQL> analyze table tab compute statistics for columns b size 10;

Table analyzed.

SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_histograms where table_name = 'TAB';

TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ -------------------- --------------- --------------

TAB                            B                                  1              1

TAB                            B                                  2              2

TAB                            B                                  3              3

TAB                            B                                  4              4

TAB                            B                               9995              5

TAB                            B                               9996           9996

TAB                            B                               9997           9997

TAB                            B                               9998           9998

TAB                            B                               9999           9999

TAB                            B                              10000          10000

直方图中的ENDPOINT_VALUE表示列值，ENDPOINT_NUMBER表示累积的行数。比如ENDPOINT_VALUE=2，ENDPOINT_NUMBER=2，因为ENDPOINT_NUMBER是个累积值，实际上2的ENDPOINT_NUMBER应该是2减去上一个值的ENDPOINT_NUMBER，也即是2-1=1。同理，5的ENDPOINT_NUMBER=9995-4=9991。

SQL> select * from tab where b=3;

1 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 439197569

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |     6 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |     1 |     6 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

        178  recursive calls

          0  db block gets

         28  consistent gets

          0  physical reads

          0  redo size

        462  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> select * from tab where b=5;

9991 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1995730731

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  9991 | 59946 |     6   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TAB  |  9991 | 59946 |     6   (0)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        689  consistent gets

          0  physical reads

          0  redo size

     174757  bytes sent via SQL*Net to client

       7711  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       9991  rows processed

这时可以看出，不同值的分布导致了ORACLE优化器选择了不同执行计划。对于b=5的查询来说，全表扫描的一致性读比之前的索引范围扫描要降低很多。可以看出此时的全表扫描比之索引范围扫描更加的合理，优化器正是根据直方图的统计信息做出的正确的判断。

上述的例子描述了一种理想的状况，因为我们为每一个不同的值创建了bucket。在实际的生产系统中，一张表可能包含很多的唯一值，我们不可能为每一个唯一值创建bucket，这样开销将是巨大的。

下面的例子描述了唯一值大于buckets的情况。

SQL> analyze table tab compute statistics for columns b size 8;

Table analyzed.

SQL> select table_name, column_name, endpoint_number, endpoint_value from dba_histograms where table_name = 'TAB';

TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ -------------------- --------------- --------------

TAB                            B                                  0              1

TAB                            B                                  7              5

TAB                            B                                  8          10000

ENDPOINT_NUMBER是实际的bucket编号，ENDPOINT_VALUE是根据列值决定的该bucket的endpoint值。上面的输出中，bucket 0存放着b列的低值，为了节省空间没有显示出1-6号的bucket。但是我们能够理解，bucket[1-7]里存放着的endpoint=5，而bucket8里存放endpoint=10000。因此，实际上bucket0里包含了1-5之间的所有值，而bucket8里包含了5-10000之间的所有值，在本例中也就是9996-10000这5个数值。

综上所述，假如数据是均衡的，没有必要使用直方图。如果使用唯一值数量来创建直方图，ORACLE为每个值创建一个bucket；但是假如实际的生产系统中，不能够为每一个唯一值分配一个bucket时，ORACLE采用合适的算法尽可能将值平均分布到每个bucket中，剩余的值放入到最后的bucket。

九、使用直方图的注意事项

1、ORACLE不能保证在join中可以充分使用histograms，如果你有一个列col，ORACLE只有你明确的指定了col operation(<,>,=,in,between 等等) 常量（这个常量当然也可以是通过bind variable peeking 获得的）的时候，才会使用histograms。如下所示：

select

t1.v1, t2.v1

from

t1,

t2

where

t1.n2 = 99

and t1.n1 = t2.n1;

如果我们在t1和t2上都有histograms，ORACLE会在t1.n2=99 这个条件上使用histograms，但ORACLE不能在and t1.n1 = t2.n1 这个条件上使用histograms，当然如果我们的条件改成：

t1.n2 = 99

and t1.n2 = t2.n1

这时候histograms 就可以使用了，因为ORACLE会自己把这个SQL 改写成：

t1.n2 = 99

and

t2.n1 = 99

2、ORACLE在分布式查询中，即通过DBLINK 查询远程数据库表，此时不会使用远程表的histograms 信息。

3、在创建高度均衡直方图时的例外情况：

即使在粒度最细的情况下，一个桶也只能大约表示某个值所对应行的1/1250（0.4%）。如果表中存在大于250 个不同值时，那么直方图肯定会漏掉一些值。实际情况会更糟糕，如果某一行可能会跨接近两个桶（行数目的0.8%），是无法被ORACLE认定为高频出现值的。更糟糕的是，如果存在某个出现频率非常高的值所跨越的桶数超过了平均数，此时就会导致ORACLE遗漏掉许多本来会被认定为高频出现的数据行。因此确切估计桶数和选择创建直方图列对于高度均衡直方图来说非常重要。

十、试验证明---有直方图执行计划更加准确

SQL> create table t_xff as select * from dba_objects;

Table created

SQL> create index ind_t_xff on t_xff(object_id) online nologging;

Index created

SQL> SELECT MAX(object_id),MIN(object_id) FROM t_xff;

MAX(OBJECT_ID) MIN(OBJECT_ID)

-------------- --------------

76800 2

SQL> UPDATE t_xff SET object_id=1000 WHERE object_id>100 AND object_id<76000;

72965 rows updated

SQL> commit;

Commit complete

SQL> BEGIN

DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF');

END;

/

PL/SQL procedure successfully completed

SQL> SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';

TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A

----- ---------- --------------- -------------- ----------

T_XFF OBJECT_ID 1 2

T_XFF OBJECT_ID 2 3

……

T_XFF OBJECT_ID 73205 76789

T_XFF OBJECT_ID 73206 76800

SQL> SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';

COLUMN_NAME HISTOGRAM

------------------------------ ---------------

OBJECT_ID FREQUENCY

在gather_table_stats方法中，默认的method_opt值为：FOR ALL COLUMNS SIZE AUTO，所以也是会收集直方图的统计信息（和oracle版本相关）

注意：ENDPOINT_NUMBER ，ENDPOINT_VALUE 的分布情况

SQL> set autot trace exp stat

SQL> select object_name from t_xff where object_id=100;

执行计划

----------------------------------------------------------

Plan hash value: 2950241517

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_XFF | 1 | 29 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T_XFF | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=100)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

432 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select object_name from t_xff where object_id=1000;

已选择72965行。

执行计划

----------------------------------------------------------

Plan hash value: 667573674

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 72965 | 2066K| 292 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T_XFF | 72965 | 2066K| 292 (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=1000)

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

5833 consistent gets

16 physical reads

0 redo size

2487154 bytes sent via SQL*Net to client

53920 bytes received via SQL*Net from client

4866 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

72965 rows processed

观察发现，因为有直方图的存在，oracle会只能的选择使用index或者全表扫描

SQL> BEGIN

DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF',method_opt => 'FOR ALL COLUMNS SIZE 1');

END;

/

PL/SQL procedure successfully completed

删除直方图，设置method_opt：FOR ALL COLUMNS SIZE 1即可

SQL> SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';

TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A

----- ---------- --------------- -------------- ----------

T_XFF OBJECT_ID 0 2

T_XFF OBJECT_ID 1 76800

SQL> SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';

COLUMN_NAME HISTOGRAM

------------------------------ ---------------

OBJECT_ID NONE

SQL> select object_name from t_xff where object_id=100;

执行计划

----------------------------------------------------------

Plan hash value: 2950241517

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 303 | 8787 | 7 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_XFF | 303 | 8787 | 7 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T_XFF | 303 | | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=100)

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

432 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select object_name from t_xff where object_id=1000;

已选择72965行。

执行计划

----------------------------------------------------------

Plan hash value: 2950241517

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 303 | 8787 | 7 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_XFF | 303 | 8787 | 7 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T_XFF | 303 | | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=1000)

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

5833 consistent gets

0 physical reads

0 redo size

2487154 bytes sent via SQL*Net to client

53919 bytes received via SQL*Net from client

4866 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

72965 rows processed

没有了直方图，oracle傻瓜的选择也使用index

虽然两次逻辑读一样，但是全表扫描涉及到一次可以读多块，但是index扫描一次只能读一个数据块。