ORACLE直方图（转）

 

一. 何谓直方图：

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

既然直方图是一种对被管理对象某一方面质量进行管理的描述工具，那么在Oracle 中自然它也是对Oracle 中某个对象质量的描述工具，这个对象就是Oracle 中最重要的东西——“ 数据” 。
在Oracle 中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够 指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL 语句执行成本最低从而提升性能。

三.Oracle 中使用直方图的场合：

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

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

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

四. 直方图有两种类别，等频直方图与等高直方图

默认的，如果一个倾斜列上的唯一值超过了254 个，那么ORACLE 会对此列建立等高直方图，否则建立等频直方图。
通过如下方式，建立表TAB ，更新字段B ，让列B 产生倾斜。并在B 列上创建索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);

表已创建。

SQL>
SQL> begin
  2         for i in 1..10000 loop
  3           insert into tab values (i, i);
  4         end loop;
  5         commit;
  6       end;
  7       /

PL/SQL 过程已成功完成。

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

已更新9990 行。
SQL> commit;

提交完成。

SQL> create index ix_tab_b on tab(b);

索引已创建。


然后分析表，强制使列B 不产生直方图。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 1 ');
END;
查看视图USER_TAB_HISTOGRAMS ，列B 上只有最大值，最小值两条记录分别对应端点号（endpoint_number ）0 和1 ，这种显示说明列B 没有直方图信息。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ；

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                            B                                                      0              1
TAB                            B                                                      1          10000

在没有直方图的情况下，在B 列上进行等值查询的时候，都是索引范围扫描。

SQL> select * from tab where b=1;


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

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  1000 |  6000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |  1000 |  6000 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


SQL> select * from tab where b=5;

已选择9991 行。


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

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  1000 |  6000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |  1000 |  6000 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
收集直方图信息。看看是什么效果。由于列B 唯一值的个数没有超过254 因此产生的是等频直方图。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR ALL COLUMNS  SIZE AUTO ');
END;

在B=1 时候采用索引扫描，而B=5 时候，已经采用全表扫描了，说明直方图起了作用。
SQL> select * from tab where b=1;


执行计划
----------------------------------------------------------
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 |
----------------------------------------------------------------------------------------

SQL> select * from tab where b=5;

已选择9991 行。


执行计划
----------------------------------------------------------
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 |
--------------------------------------------------------------------------



查看此时的直方图信息：
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_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

其中EDNPOINT_NUMBER 是累计值。EDNPOINT_VALUE 是列的值。可以看出这种等频直方图统计的列 的信息是非常精确的。它为每一个列值分配了一个桶。从执行计划的ROWS 部分也可以看出ORACLE 计算出来的cardinality 是9991 ，和实际 的情况完全吻合。
如果想知道每一个列值对应的数量是多少，需要做一下简单的减法运算：
假如想知道列值等于5 的个数，那么可以通过：
9995-4=9991 得到。这就是ENDPOINT_NUMBER 累计值的含义。

在看看等高直方图的情况。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 8 ');
END;

由于列B 有10 个唯一值，通过上面的size 8 可以强制ORACLE 使用等高直方图。
查看直方图信息.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_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

从查询结果惊奇的发现只有三个桶0 7 8 ，原来ORACLE 会自动省去EDNPOINT_VALUE 值相同且ENDPOINT_NUMBER 相邻的桶的值。
省去了桶（EDNPOINT_NUMBER ）为1 2 3 4 5 6 ，EDNPOINT_VALUE 为5 的六条内容。


说明：在等高直方图中，EDNPOINT_NUMBER 代表桶号, 这一点与等频直方图不同。

再看等高直方图下的执行计划：

SQL> select * from tab where b=5;
已选择9991 行。

执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9982 | 59892 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB  |  9982 | 59892 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------


有没有发现什么？

执行计划的ROWS 部分，ORACLE 计算出来的cardinality 不是特别精确的。9991 才是精确值。而等频直方图可以精确到9991 ，因此可以说等频直方图比等高直方图稳定，精确。
可是现实很多时候，列的唯一值是超过254 的。只能使用等高直方图了。

> 1. 如果列B 有新的数据插入或者更新，直方图会随之更新吗？

It won't automatically. You have to manually gather stats again. Your DB may have a 10pm nightly job to do it too.

> 2. 在列B 上建位图索引能否解决同样的问题？

A bitmap index? Why do you think so?

> 10.2.0.4 中在建立直方图后，还是使用索引，请问哪位知道是为什么？

Can you show us the sqlplus screen (text, not image)? I'd like to reproduce on my end.