【精品篇】_Oracle的直方图是个啥？

一森咖记 2020-01-13

1120

【引言】

在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。

构造直方图最主要的原因：是帮助优化器在表中数据严重偏斜时做出更好的规划

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

当where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。

在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。

构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划 。如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。

通常情况下在以下场合中建议使用直方图：

1、当Where子句引用了列值分布存在明显偏差的列时

当这种偏差相当明显时，以至于WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。

注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。

2、当列值导致不正确的判断时

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

等频直方图与等高直方图

默认的，如果一个倾斜列上的唯一值超过了254个，那么ORACLE会对此列建立等高直方图，否则建立等频直方图。

先来看下等频直方图。

所谓的等频即按照列上的不同数据值进行划分，由于每个数值的频度相同，高度不同，故称为等频。下面是具体例子：

建立表TAB，更新字段B，让列B产生倾斜。并在B列上创建索引。

SQL> create table tab(a number,b number);
Table created.
SQL> insert into tab select rownum,rownum from dual connect by level <=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
SQL> create index tab_b_idx on tab(b);
Index created.

然后分析表，强制使列B不产生直方图。

SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
PL/SQL procedure successfully completed.

method_opt参数的官方解释如下

method_opt

Accepts either of the following options, or both in combination:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | extension name | extension- integer : Number of histogram buckets. Must be in the range [1,254].- REPEAT : Collects histograms only on the columns that already have histograms- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.- column_name : Name of a column- extension : can be either a column group in the format of (column_name, Colume_name [, ...]) or an expression

查看视图USER_TAB_HISTOGRAMS/USER_HISTOGRAMS 或者DBA_TAB_COL_STATISTICS

SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where TABLE_NAME='TAB'
TABLE_NAME   COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB          B                          0              1
TAB          B                          1          10000

注意：上述显示列B上只有最大值，最小值两条记录分别对应端点号(endpoint_number)0和1，这种显示说明列B没有直方图信息。在没有直方图的情况下，在B列上进行等值查询的时候，都是索引范围扫描。

SQL>  select * from tab where b=5
Execution Plan
----------------------------------------------------------
Plan hash value: 157166354
-----------------------------------------------------------------------------------------
| 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          | TAB_B_IDX |  1000 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"=5)

执行统计信息收集，收集直方图信息，查看执行效果。

由于列B唯一值的个数没有超过254，因此这时产生的是等频直方图。

SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true);
PL/SQL procedure successfully completed.

默认是对所有列分析直方图

在B=1时候采用索引扫描，而B=5时候，已经采用全表扫描了，说明直方图起了作用。

SQL> select * from tab where b=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 157166354
-----------------------------------------------------------------------------------------
| 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          | TAB_B_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"=1)

SQL> select * from tab where  b=5;
 
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 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=5)

查看此时的直方图信息：

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              1TAB          B                          2              2TAB          B                          3              3TAB          B                          4              4TAB          B                       9995              5TAB          B                       9996           9996TAB          B                       9997           9997TAB          B                       9998           9998TAB          B                       9999           9999TAB          B                      10000          10000 TAB          A                          0              1TABLE_NAME   COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE------------ ------------ --------------- --------------TAB          A                          1          1000012 rows selected.

其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的列的信息是非常精确的。它为每一个列值分配了一个bucket。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991，和实际的情况完全吻合。

如果想知道每一个列值对应的数量是多少，需要做一下简单的减法运算：假如想知道列值等于5的个数，那么可以通过：9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。

等高直方图，当列上的数据不同值超过254时，Oracle将会默认将列上的数据划分为高度一致但频度不一致的等高直方图。

SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 8);
PL/SQL procedure successfully completed.

由于列B有10个唯一值，通过上面的size 8可以强制ORACLE使用等高直方图。

查看直方图信息.

SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_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
TAB          A                          0              1
TAB          A                          1          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
Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=5)

有执行计划的ROWS部分，ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991，因此可以说等频直方图比等高直方图稳定，精确。

可是现实很多时候，列的唯一值是超过254的。只能使用等高直方图了。

直方图有创建就有删除，如何删除直方图。

11g有如下方法可以直接删除直方图信息

dbms_stats.delete_column_stats(ownname => user,
tabname => 'T',
colname => 'VAL',
col_stat_type => 'HISTOGRAM');

至此文章结束。

【总结】

1. 在Oracle中直方图是一种对数据分布质量情况进行描述的工具。一句话直方图的作用：是帮助CBO选择出更准确的执行计划；

2. 通常情况下在以下场合中建议使用直方图：1、当Where子句引用了列值分布存在明显偏差的列时，这时应该使用直方图来帮助优化器来修正执行路径；如果查询不引用该列，则创建直方图没有意义；2、当列值导致不正确的判断时，这种情况通常会发生在多表连接时，添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

3. 通过实验介绍了两种形式的直方图：等频直方图与等高直方图。

【参考】

http://www.itpub.net/thread-1350285-1-1.html

【参考】

http://chenxy.blog.51cto.com/729966/743065

以下是个人微信公众号“一森咖记”，欢迎关注

往期精彩文章

========================================

Oracle ADG同步技术，DBA必备的一种“后悔药”
Oracle 11g 异机rman恢复报错ORA-27302:failure occurred at: sskgpcreates
年末总结_聊一聊数据库行业的“继往开来”
Materialized view物化视图的一个简单应用场景
干货：RHEL7.2生产环境下双节点12c RAC搭建实操
【干货篇】在国内外数据库百家争鸣的时代，DBA们该何去何从？
LINUX环境：MySQL和Oracle开机自启动，咋搞？
Logminer：oracle人为误操作之恢复神器
What：ASM自动脱落了
实操：12C RAC环境下的ADG同步库搭建
“神器”：Oracle日志采集分析工具——TFA
Oracle Rac：关闭透明大页的原因及方法
实操篇：Oracle 19c的安装部署
MySQL：主从同步延迟Seconds_Behind_Master越来越大，什么鬼？
浅谈MySQL三种锁：全局锁、表锁和行锁
Oracle如何访问MySql：透明网关

oracle

最后修改时间：2020-01-14 10:50:40

文章转载自一森咖记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【精品篇】_Oracle的直方图是个啥？

评论