文章详情页_Oracle 直方图

直方图是一种统计学上的工具，并非 Oracle 专有。通常用于对被管理对象的某个方面的质量情况进行管理，一般它会表现为一种几何图形表，这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的，通常会画成以数量为底边，以频度为高度的一系列连接起来的矩形图，因此直方图在统计学上也称为质量分布图。

直方图的含义

在 Oracle 数据库中， CBO 会默认认为目标列的数据在其最小值 LOW_VALUE 和最大值 HIGH_VALUE 之间是均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的 Cardinality，进而据此来计算成本值并选择执行计划。

但目标列的数据是均匀分布这个原则并不总是正确的，在实际的关系中，我们很容易就能看到有一些目标列的数据是不均匀的，甚至极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与 Cardinality，并据此来计算成本、选择执行计划，那么 CBO 所选择的执行计划就可能是不合理的，甚至是错误的。

对于单表查询，直方图信息是优化器判断能否使用索引的重要依据。对于多表关联，直方图信息是用来准确估算 Cardinality 的重要依据，会影响两表关联时驱动表的选择，如果两个表关联后还要再跟其他表做关联，Cardinality 估值偏差会比较大，将会导致优化器选择的执行计划可能不是最优。

我们来看一个由于数据分布极不均衡而导致 CBO 选错执行计划的例子。

创建测试表 T1

SQL> create table t1 (a number(5), b varchar2(5));

表已创建。

插入 10001 条记录，其中列 B 的值有 10000 条记录均为 1，只有一条记录的值为 2

declare
  cnt number(5) := 1;
begin
  loop
    insert into t1 values(1, 1);
    if cnt = 10000 then 
      exit;
    end if;
    cnt := cnt + 1;
  end loop;
  insert into t1 values(2, 2);
  commit;
end;
/


SQL> select b, count(*) from t1 group by b;

B       COUNT(*)
----- ----------
1          10000
2              1

在列 B 上创建一个单键值的 B-Tree 索引 IDX_T1

SQL> create index idx_t1 on t1(b);

索引已创建。

对表 T1 以不收集直方图的方式收集一下统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'T1',
                                estimate_percent => 100,
                                method_opt => 'for all columns size 1');
end;
/

执行如下 SQL，并查看其执行计划

SQL> set autotrace traceonly;

SQL> select * from t1 where t1.b = '2';

histogram01

我们知道在表 T1 中列 B 只有一条记录的值为 2，我们在执行上述 SQL 查询的时候应该走列 B 上的索引 IDX_T1，但实际上 CBO 这里却选择了全表扫描。

这是因为 CBO 默认认为列 B 的数据是均匀分布的，而列 B 上的 DISTINCT 值只有 1 和 2 这两个值，所以 CBO 评估出来的对列 B 施加等值查询条件的可选择率就是 1/2，进而评估出来的对列 B 施加等值查询条件的结果集的 Cardinality 就是 5001。

SQL> select round(10001*(1/2)) from dual;

ROUND(10001*(1/2))
------------------
              5001

正是因为 CBO 评估出上述等值查询要返回结果集的 Cardinality 是 5001，已经占了表 T1 的总记录数的一半，所以 CBO 认为此时再走列 B 上的索引 IDX_T1 就已经不合适了，进而选择了走对表 T1 的全表扫描。但实际上，CBO 对上述等值查询要返回结果集的 Cardinality 的评估已经与事实严重不符，评估出来的值是 5001，其实却只有 1，差了好几个数量级。

CBO 这里选错了执行计划，正确的执行计划应该是走索引 IDX_T1。CBO 选错执行计划的根本原因就是表 T1 的列 B 的数据分布实际上是极度不均衡的（列 B 一共只有 2 个值，其中有 10000 个 1，只有 1 个 2），CBO 在评估的一开始所用的原则就错了，当然结果也就错了。

为了解决上述问题，Oracle 引入了直方图。直方图是一种特殊的列统计信息，它详细描述了目标列的数据分布情况。直方图实际上存储在数据字典基表 HISTGRM$ 中，可以通过数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS 来分布查看表、分区表的分区和分区表的子分区的直方图信息。

如果对目标列收集了直方图，则意味着 CBO 将不再认为该目标列上的数据是均匀分布的了，CBO 就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的 Cardinality，进而据此计算成本并选择相应的执行计划。

对表 T1 我们继续执行如下查询：

查看表 T1 的列 B 的直方图统计信息

SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'T1' 
  and column_name = 'B';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- ---------------
T1                   B                               2           1 NONE

对表 T1 以自动收集直方图的方式收集一下统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'T1',
                                estimate_percent => 100,
                                method_opt => 'for all columns size auto');
end;
/

再次查看表 T1 的列 B 的直方图统计信息

SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'T1' 
  and column_name = 'B';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- ---------------
T1                   B                               2           2 FREQUENCY

执行上面相同的 SQL 查询，并查看其执行计划

SQL> set autotrace traceonly;

SQL> select * from t1 where t1.b = '2';

histogram02

在对表 T1 的列 B 收集了直方图统计信息后，从上述执行计划可以看到，此时 CBO 正确的评估出了返回结果集的 Cardinality 不是 5001，而是 1，进而正确地选择了走索引 IDX_T1 的执行计划。

所以，直方图就是专门为了准确评估这种分布不均匀的目标列的可选择率、结果集的 Cardinality 而被 Oracle 引入的，它详细描述了目标列的数据分布情况，并将这些分布情况记录在数据字典里，相当于直观地告诉了 CBO 这些列的数据分布情况，于是 CBO 就能据此来做出相对准确的判断。

直方图的类型

Oracle 数据库里的直方图使用了一种称为 Bucket（桶）的方式来描述目标列的数据分布。它实际上是一个逻辑概念，相当于分组，每个 Bucket 就是一个组，每个 Bucket 里会存储一个或多个目标列中的数据。Oracle 会用两个维度来描述一个 Bucket，这两个维度分别是 ENDPOINT NUMBER 和 ENDPOINT VALUE。

Oracle 会将每个 Bucket 的维度 ENDPOINT NUMBER 和 ENDPOINT VALUE 记录在数据字典基表 HISTGRM$ 中，这样就达到了将目标列的直方图统计信息记录在数据字典中的目的。维度 ENDPOINT NUMBER 和 ENDPOINT VALUE分别对应于数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS 中的字段 ENDPOINT_NUMBER/BUCKET_NUMBER 和 ENDPOINT_VALUE。同时，Oracle 还会记录目标列的直方图统计信息所占用的 Bucket 的总数，可以通过数据字典 DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS 和 DBA_SUBPART_COL_STATISTICS 中的字段 NUM_BUCKETS 来查看目标列对应直方图的 Bucket 的总数。

在 Oracle 12c 之前，Oracle 数据库里的直方图分为两种类型，分别是 Frequency 和 Height Balanced。

Frequency 类型的直方图

对于 Frequency 类型的直方图而言，目标列直方图的 Bucket 的数量就等于目标列的 DISTINCT 值的数量，此时目标列由多少个 DISTINCT 值，Oracle 在数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS（分别对应于表、分区表和子分区的直方图统计信息）中就会存储多少条记录，每一条记录就代表了对其中的一个 Bucket 的描述，上述数据字典中的字段 ENDPOINT VALUE 记录了这些 DISTINCT 值，而字段 ENDPOINT NUMBER 则记录了到此 DISTINCT 值为止总共有多少条记录。

Frequency 类型的直方图所对应的收集方法并不适用于目标列的 DISTINCT 值非常多的情形，所以 Oracle 对 Frequency 类型的直方图有如下的限制： Frequency 类型的直方图所对应的 Bucket 的数量不能超过 254，即 Frequency 类型的直方图只适用于那些目标列的 DISTINCT 值小于或等于 254 的情形（需要注意的是，Oracle 12c 中将不再有这一限制，在 Oracle 12c 中 Frequency 类型的直方图所对应的 Bucket 的数量可以超过 254）

下面来举一个例子来详细说明 Frequency 类型的直方图。

创建测试表 S1，它只有一个列 ID

SQL> create table s1 (id number);

表已创建。

插入 40000 条记录，这 40000 条记录总共有 10 个 distinct 值，它们分别是 1、3、5、7、10、16、27、32、39、49

declare
  i number;
begin
  for i in 1 .. 3296 loop
    insert into s1 values(1);
  end loop;

  for i in 1 .. 100 loop
    insert into s1 values(3);
  end loop;

  for i in 1 .. 798 loop
    insert into s1 values(5);
  end loop;

  for i in 1 .. 3970 loop
    insert into s1 values(7);
  end loop;

  for i in 1 .. 16293 loop
    insert into s1 values(10);
  end loop;

  for i in 1 .. 3399 loop
    insert into s1 values(16);
  end loop;   

  for i in 1 .. 3651 loop
    insert into s1 values(27);
  end loop;  

  for i in 1 .. 3892 loop
    insert into s1 values(32);
  end loop;  

  for i in 1 .. 3521 loop
    insert into s1 values(39);
  end loop;    

  for i in 1 .. 1080 loop
    insert into s1 values(49);
  end loop;

  commit;          
end;
/

对表 S1 的列 ID 收集直方图统计信息（数据分布严重倾斜）

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'S1',
                                estimate_percent => 100,
                                method_opt => 'for columns size auto ID',
                                cascade =>  true);
end;
/

使用 DBMS_STATS.GATHER_TABLE_STATS 方法收集统计信息时，对输入参数 METHOD_OPT 指定值 for columns size auto ID 就可实现这个任务。for columns size auto ID 表示对目标表的列 ID 收集直方图统计信息，这里 auto 的含义是指让 Oracle 自行决定到底是否对列 ID 收集直方图以及使用哪种类型的直方图。

查看直方图统计信息

SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S1' 
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- ---------------
S1                   ID                             10           1 NONE

收集完统计信息后，我们发现 DBA_TAB_COL_STATISTICS 中列 ID 所对应的字段 HISTOGRAM 的值为 NONE，这表明在列 ID 上依然没有直方图统计信息。

这种情况是正常的，因为 Oracle 在自动收集直方图统计信息时会秉承一个原则，那就是只对那些用过的列（即在 SQL 语句的 where 条件中出现过的列）收集直方图统计信息，Oracle 会在表 SYS.COL_USAGE$ 中记录各表中各列的使用情况，在自动收集直方图统计信息时 Oracle 会去查询 SYS.COL_USAGE$，如果发现其中没有目标列的相关使用记录，那就不会对目标列收集直方图统计信息。表 S1 刚刚创建，还没有在 SQL 语句的 where 条件中使用列 ID，所以这里 Oracle 并不会对列 ID 收集直方图统计信息。

获取表 S1 的 OBJECT_ID

SQL> select object_id from dba_objects where object_name = 'S1';

 OBJECT_ID
----------
     88868

获取表 S1 的列 ID 所对应的 INTCOL#

SQL> select name, intcol# from sys.col$ where obj# = 88868;

NAME                              INTCOL#
------------------------------ ----------
ID                                      1

确认 SYS.COL_USAGE$ 中是否有列 ID 的使用记录

SQL> select obj#, intcol#, equality_preds from sys.col_usage$ where obj# = 88868;

未选定行

使用表 S1 的列 ID

SQL> select count(*) from s1 where id = 10;

  COUNT(*)
----------
     16293

再次对表 S1 的列 ID 自动收集直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'S1',
                                estimate_percent => 100,
                                method_opt => 'for columns size auto ID',
                                cascade =>  true);
end;
/

查看 SYS.COL_USAGE$ 中是否已经有了 OBJ# 为88868，INTCOL# 为 1 的列（即列 ID）的使用记录

SQL> select obj#, intcol#, equality_preds from sys.col_usage$ where obj# = 88868;

      OBJ#    INTCOL# EQUALITY_PREDS
---------- ---------- --------------
     88868          1              1

查看列 ID 上是否已经有了 Frequency 类型的直方图

SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S1' 
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- ------------- 
S1                   ID                             10          10 FREQUENCY

查看列 ID 的 Frequency 类型的直方图的具体信息

SQL> col table_name for a20;
SQL> col column_name for a20;


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

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
S1                   ID                              3296              1
S1                   ID                              3396              3
S1                   ID                              4194              5
S1                   ID                              8164              7
S1                   ID                             24457             10
S1                   ID                             27856             16
S1                   ID                             31507             27
S1                   ID                             35399             32
S1                   ID                             38920             39
S1                   ID                             40000             49

已选择10行。

从上述结果集中可以看到，现在 DBA_TAB_HISTOGRAMS 中有 10 条记录，它们的 ENDPOINT_VALUE 记录的就是这 10 个 DISTINCT 值，对应的 ENDPOINT_NUMBER 就是此 DISTINCT 值为止累加的行记录数。

Height Balanced 类型的直方图

在介绍 Frequency 类型的直方图时已经提到，在 Oracle 数据库里（Oracle 12c 之前），Frequency 类型的直方图所对应的 Bucket 的数量不能超过 254，即 Frequency 类型的直方图只适用于那些目标列的 DISTINCT 值的数量小于或等于 254 的情形。

如果目标列的 DISTINCT 值大于 254，此时 Oracle 会对目标列收集 Height Balanced 类型的直方图。

下面我们来看一个 Frequency 类型的直方图转为 Height Balanced 类型直方图的实例。

创建测试表 S2

SQL> create table s2 (id number);

表已创建。

插入数据，使得列 ID 上的 DISTINCT 值的数量为 254（注意数据分布必须倾斜）

declare
  i number;
  j number;
begin
  for i in 1 .. 254 loop
    j := 1;
    while (j <= i) loop
      insert into s2 values(i);
      j := j + 1;
    end loop;
  end loop;
  commit;
end;
/


SQL> select count(distinct id) from s2;

COUNT(DISTINCTID)
-----------------
              254

在对表 S2 的列 ID 收集直方图统计信息之前，先在 SQL 语句的 where 条件中使用列 ID

SQL> select count(*) from s2 where id = 25;

  COUNT(*)
----------
       254

对列 ID 以自动收集直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'S2',
                                estimate_percent => 100,
                                method_opt => 'for columns size auto id',
                                cascade =>  true);
end;
/

查看列 ID 上是否已经有了 Frequency 类型的直方图

SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S2' 
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- ------------
S2                   ID                            254         254 FREQUENCY

对表 S2 再插入一条包含不同 ID 值的记录

SQL> insert into s2 values(255);

已创建 1 行。


SQL> commit;

提交完成。

现在列 ID 上的 DISTINCT 值数量为 255，这已经超过了 Frequency 类型直方图所能处理的 DISTINCT 值的上限

SQL> select count(distinct id) from s2;

COUNT(DISTINCTID)
-----------------
              255

删除列 ID 上的直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'S2',
                                estimate_percent => 100,
                                method_opt => 'for columns size 1 id',
                                cascade =>  true);
end;
/


SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S2' 
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- -----------
S2                   ID                            255           1 NONE

对列 ID 以自动方式重新收集直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'S2',
                                estimate_percent => 100,
                                method_opt => 'for columns size auto id',
                                cascade =>  true);
end;
/


SQL> select  
  table_name,
  column_name,
  num_distinct,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S2' 
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ----------- ---------------
S2                   ID                            255         254 HEIGHT BALANCED

从上面查询的结果可以看到，现在列 ID 上的直方图类型已经从之前的 Frequency 变成了 Height Balanced。

直方图的收集方法

在 Oracle 数据里收集直方图统计信息，通常是在调用 DBMS_STATS 包中的存储过程 GATHER_DATABASE_STATS、GATHER_DICTIONARY_STATS、GFATHER_SCHEMA_STATS、GATHER_TABLE_STATS 收集统计信息时通过输入参数 METHOD_OPT 来实现的。

DBMS_STATS 包中上述存储过程的输入参数 METHOD_OPT 可以接受如下的输入值

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause] column|attribute [size _clause] [,column|attribute [size_clause] ... ]

其中的 size_clause 必须符合如下格式

SIZE [integer | REPEAT | AUTO | SKEWONLY]

size_clause 子句中各选项的含义如下所述：

integer：直方图的 Bucket 的数量，必须是在 1 ~ 254 的范围内，1 表示删除该目标列上的直方图统计信息
REPEAT：只对已经有直方图统计信息的列收集直方图统计信息
AUTO：让 Oracle 自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图
SKEWONLY：只对数据分布不均衡的列收集直方图统计信息

下面我们来使用上述方法收集直方图统计信息，这里以 SCOTT 用户下的表 EMP 为例来说明

对表 EMP 上所有有索引的列以自动收集的方式收集直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'EMP',
                                estimate_percent => 100,
                                method_opt => 'for all indexed columns size auto',
                                cascade =>  true);
end;
/

对表 EMP 上的列 EMPNO 和 DEPTNO 以自动收集的方式收集直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'EMP',
                                estimate_percent => 100,
                                method_opt => 'for columns size auto EMPNO DEPTNO',
                                cascade =>  true);
end;
/

对表 EMP 上的列 EMPNO 和 DEPTNO 收集直方图统计信息，同时指定 Bucket 数量均为 10

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'EMP',
                                estimate_percent => 100,
                                method_opt => 'for columns size 10 EMPNO DEPTNO',
                                cascade =>  true);
end;
/

对表 EMP 上的列 EMPNO 和 DEPTNO 收集直方图统计信息，同时指定列 EMPNO 的 Bucket 数量为 10，列 DEPTNO 的 Bucket 数量为 5

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'EMP',
                                estimate_percent => 100,
                                method_opt => 'for columns EMPNO size 10 DEPTNO size 5',
                                cascade =>  true);
end;
/

只删除表 EMP 上列 EMPNO 的直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'EMP',
                                estimate_percent => 100,
                                method_opt => 'for columns EMPNO size 1',
                                cascade =>  true);
end;
/

删除表 EMP 上所有列的直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'EMP',
                                estimate_percent => 100,
                                method_opt => 'for all columns size 1',
                                cascade =>  true);
end;
/

直方图对 CBO 的影响

如果对目标列收集了直方图，则意味着 CBO 不再认为目标列上的数据是均匀分布的。CBO 会用该列上的直方图的统计信息计算返回结果集的 Cardinality。

直方图对可选择率的影响

直方图统计信息就是专门为了准确评估分布不均匀的目标列的可选择率、结果集的 Cardinality 而被引入的。即当目标列上有了直方图统计信息后，CBO 将不再认为该目标列上的数据是均匀分布的，此时 CBO 就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和对应结果集的 Cardinality，进而据此来计算成本并选择相应的执行计划。所以，直方图对 CBO 估算可选择率、计算成本及选择执行计划是有直接影响的。

下面我们来看一个直方图对可选择率影响的实例。数据字典 DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS 和 DBA_SUBPART_COL_STATISTICS 中的字段 DENSITY 的值实际上可以近似看做是对目标列施加查询条件后的可选择率，在没有直方图统计信息时，DENSITY 的值就等于 1/NUM_DISTINCT。

表 S1 的总记录数为 40000，包含 10 个 DISTINCT 值（1、3、5、7、10、16、27、32、39、49）。

当表 S1 的列 ID 没有直方图统计信息时，DENSITY 的值就等于 1/NUM_DISTINCT = 1/10 = 0.1

SQL> select  
  table_name,
  column_name,
  num_distinct,
  density,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S1'  
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT    DENSITY NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ---------- ----------- ---------------
S1                   ID                             10        0.1           1 NONE

即当表 S1 的列 ID 没有直方图统计信息时，对目标列 ID 施加等值查询条件后的可选择率就是 0.1，所以执行如下两条 SQL 语句时，CBO 评估出来的结果集的 Cardinality 应该都是 40000*0.1=4000

SQL> select count(*) from s1 where id = 10;

  COUNT(*)
----------
     16293


SQL> select count(*) from s1 where id = 9;

  COUNT(*)
----------
         0

上述两条 SQL 语句所对应的执行计划分别为

SQL> set autotrace traceonly;


SQL> select count(*) from s1 where id = 10;

执行计划
----------------------------------------------------------
Plan hash value: 1396819819

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| S1   |  4000 | 12000 |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=10)



SQL> select count(*) from s1 where id = 9;

执行计划
----------------------------------------------------------
Plan hash value: 1396819819

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| S1   |  4000 | 12000 |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=9)

从上述执行计划的 ROWS 列可以看到，现在 CBO 对上述两条 SQL 语句评估出来的 Cardinality 确实都是 4000，这说明 DENSITY 已经被 CBO 用于评估结果集的 Cardinality。

对表 S1 的 ID 列收集 Frequency 类型直方图统计信息

begin
  dbms_stats.gather_table_stats(ownname => 'SCOTT',
                                tabname => 'S1',
                                estimate_percent => 100,
                                method_opt => 'for columns size auto ID',
                                cascade =>  true);
end;
/

查看列 ID 的 DENSITY 值

 SQL> select  
  table_name,
  column_name,
  num_distinct,
  density,
  num_buckets,
  histogram 
from dba_tab_col_statistics  
where owner = 'SCOTT' and table_name = 'S1' 
  and column_name = 'ID';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT    DENSITY NUM_BUCKETS HISTOGRAM
-------------------- -------------------- ------------ ---------- ----------- ---------------
S1                   ID                             10    1.25E-5          10 FREQUENCY

上述 DENSITY 的值 1.25E-5 是如何计算出来的呢？当目标列上游 Frequency 类型的直方图时，其 DENSITY 的计算公式如下所示

DENSITY = 1 / (2 * NUM_ROWS * Null_Adjust)
Null_Adjust = (NUM_ROWS - NUM_NULLS) / NUM_ROWS

表 S1 有 40000 条记录，且列 ID 上没有 NULL 值，所以其 DENSITY 的值就是 1.25E-5

SQL> select 1 / (2 * 40000 * 1) from dual;

1/(2*40000*1)
-------------
      1.25E-5

在对表 S1 的 ID 列收集完 Frequency 类型直方图统计信息后，我们来测试上述 DENSITY 的值是否会被 CBO 用于评估对目标列施加等值查询条件后的结果集的 Cardinality。如果上述 DENSITY 的值会被 CBO 用于评估结果集的 Cardinality，那么当执行如下两条 SQL 语句时，CBO 评估出来的 Cardinality 就应该是 40000 * 1.25E-5=0.5，即约等于1。

SQL> select count(*) from s1 where id = 10;

  COUNT(*)
----------
     16293


SQL> select count(*) from s1 where id = 9;

  COUNT(*)
----------
         0

上述两条 SQL 语句所对应的执行计划分别为

SQL> set autotrace traceonly;


SQL> select count(*) from s1 where id = 10;

执行计划
----------------------------------------------------------
Plan hash value: 1396819819

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| S1   | 16293 | 48879 |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=10)



SQL> select count(*) from s1 where id = 9;

执行计划
----------------------------------------------------------
Plan hash value: 1396819819

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |    19   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| S1   |    50 |   150 |    19   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=9)

从上述执行计划的 ROWS 列可以看到，现在 CBO 对第 1 条 SQL 语句评估出来的 Cardinality 是 16293，对第 2 条 SQL 语句评估出来的 Cardinality 是 50，均不是 1，这说明 CBO 在这里并没有用上述 DENSITY 的值来评估结果集的 Cardinality。

那么上述 Cardinality 的值 16293 和 50 是如何计算出来的呢？当目标列有 Frequency 类型的直方图时，如果对目标列施加等值查询条件，且查询条件的输入值在目标列的最小值和最大值之间，那么其结果集的 Cardinality 的计算公式为如下所示。

（1）如果查询条件的输入值等于目标列的某个 Bucket 的 ENDPOINT_VALUE，则使用如下公式：

cardinality = NUM_ROWS * selectivity
selectivity = BucketSize / NUM_ROWS
BucketSize = Current_ENDPOINT_NUMBER - previous_ENDPOINT_NUMBER

查看表 S1 的列 ID 的 Frequency 类型直方图的详情

select 
  table_name,
  column_name,
  endpoint_number,
  endpoint_value 
from dba_tab_histograms 
where table_name = 'S1';

TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
S1                   ID                              3396              3
S1                   ID                              4194              5
S1                   ID                              8164              7
S1                   ID                             24457             10
S1                   ID                             27856             16
S1                   ID                             31507             27
S1                   ID                             35399             32
S1                   ID                             38920             39
S1                   ID                             40000             49
S1                   ID                              3296              1

已选择10行。

从上述结果集可以看到，第 1 条 SQL 语句中等值条件 "x=10" 的输入值 "10" 刚好有一个对应的 Bucket，此时 Current_ENDPOINT_NUMBER 的值为 24457，Previous_ENDPOINT_NUMBER 的值为 8164，所以输入值 10 对应的 Bucket 的值就等于 Current_ENDPOINT_NUMBER - Previous_ENDPOINT_NUMBER = 24457 - 8164 = 16293，故按照上述公式计算出来的 Cardinality 就是 16293

SQL> select 40000 * ((24457 - 8164) / 40000) from dual;

40000*((24457-8164)/40000)
--------------------------
                     16293

这里的 16293 和第 1 条 SQL 语句实际执行计划中显示的 Cardinality 一致。

（2）如果查询条件的输入值不等于目标列的任意一个 Bucket 的 ENDPOINT_VALUE，则使用如下公式：

cardinality = NUM_ROWS * selectivity
selectivity = MIN(BucketSize) / (2 * NUM_ROWS)
BucketSize = Current_ENDPOINT_NUMBER - previous_ENDPOINT_NUMBER

使用直方图的注意事项

直方图就是专门为了准确评估分布不均匀的目标列的可选择率、结果集的 Cardinality 而被 Oracle 引入的。所以，如果目标列的数据是均匀分布的，比如主键列、有唯一索引的列，则根本就不需要对这些列收集直方图统计信息。如果对这些均匀分布的列收集了直方图统计信息，由于直方图能够直接影响 CBO 估算可选择率，进而影响 CBO 对成本的计算和对执行计划的选择，那么在这种情况下就可能会使 Oracle 选错执行计划，进而影响到目标 SQL 的执行效率。
对于那些从来没有在 SQL 语句的 where 条件中出现的列，不管其数据分布是否均匀，都无须对其收集直方图统计信息。这是很显然的事情，如果目标列从未在 SQL 语句的 where 条件谓词中出现，则意味着这个列的直方图统计信息不会对 CBO 计算成本、选择执行计划产生任何影响，那还是浪费时间、浪费资源去收集这个列的直方图统计信息干嘛？实际上，Oracle 默认的自动收集直方图统计信息的策略就是采用了上述原则，如果目标列从未在 SQL 语句的 where 条件中出现过，则 SYS.COL_USAGE$ 中就不会有这个列的使用记录，那么 Oracle 在以默认方式自动收集统计信息是就不会收集这个列的直方图统计信息。
直方图统计信息可能会影响到 Shared Cursor 共享，特别是在与有绑定变量相关的 SQL 联用时。如果直方图统计信息确实影响到了目标 SQL 的 Shared Cursor 的共享，则可以在明确诊断的基础上采取相应的措施（比如删除目标列上直方图统计信息）。

参考资料

《崔华 - 基于Oracle 的 SQL 优化》

http://blog.itpub.net/26015009/viewspace-1072732/

原创文章，转载请注明出处：http://www.opcoder.cn/article/29/