合 Oracle中的统计信息之直方图（Histogram）系列
简介
直方图是CBO中的一个重点，也是一个难点部分，在面试中常常被问到。
直方图的意义
在Oracle数据库中，CBO会默认认为目标列的数据在其最小值（LOW_VALUE）和最大值（HIGH_VALUE）之间是均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加WHERE查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划。但是，目标列的数据是均匀分布这个原则并不总是正确的，在实际的生产系统中，有很多表的列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就很可能是不合理的，甚至是错误的，所以，此时应该收集列的直方图。
直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况。当数据分布倾斜时，直方图可以有效地提升Cardinality评估的准确度。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划。例如，表中的某个列上，其中的某个值占据了数据行的80%（数据分布倾斜），相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。如果对目标列收集了直方图，那么意味着CBO将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。
直方图实际上存储在数据字典基表SYS.HISTGRM$中，可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
直方图的使用场合
通常情况下在以下场合中建议使用直方图：
（1）当WHERE子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于WHERE子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：若查询不引用该列，则在该列上创建直方图没有意义）。
（2）当列值导致不正确的判断时，这种情况通常会发生在多表连接时。例如，假设有一个五张表的连接操作，其目标SQL最终结果集只有10行。Oracle将会以一种使第一个连接的结果集（集合基数）尽可能小的方式将表连接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在SQL执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，则它可能会选择一种未达到最优化的表连接方法。因此向该列添加直方图经常会向优化器提供使用最佳连接方法所需的信息。
直方图的分类
Oracle数据库里的直方图使用了一种称为Bucket（桶）的方式来描述目标列的数据分布。Bucket（桶）是一个逻辑上的概念，相当于分组，每个Bucket就是一组，每个Bucket里会存储一个或多个目标列中的数据。Oracle会用两个维度来描述一个Bucket，这两个维度分别是ENDPOINT_NUMBER和ENDPOINT_VALUE，Oracle会将每个Bucket的这两个维度记录在数据字典基表SYS.HISTGRM$中。列的直方图的类型可以通过查询视图DBA_TAB_COL_STATISTICS的HISTOGRAM列来获取，一般情况下包含3类，NONE（没有直方图）、FREQUENCY（频率直方图，也叫等频直方图）、HEIGHT BALANCED（高度平衡直方图，也叫等高直方图）。在Oracle 12c中，又新增了两种类型的直方图，分别是顶级频率直方图（Top Frequency Histogram）和混合直方图（Hybrid Histogram），本书只讨论频率和高度平衡直方图。
（1）频率（Frequency，Freq）直方图
在Oracle 12c之前，在目标列的数据分布是倾斜的情况下（即存储在数据字典里的目标列的DISTINCT值的数量小于目标表的记录数），如果存储在数据字典里描述目标列直方图的Bucket的数量等于目标列的DISTINCT值的数量，那么这种类型的直方图就是频率（Frequency）直方图。频率直方图只适用于那些目标列的DISTINCT值数量小于或等于254的情形。需要注意的是，在Oracle 12c中，频率直方图所对应的Bucket的数量可以超过254。
对于频率直方图而言，目标列直方图的Bucket的数量就等于目标列的DISTINCT值的数量，此时目标列有多少个DISTINCT值，Oracle在数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS（分别对应于表、分区和子分区的直方图统计信息）中就会存储多少条记录，每一条记录就代表了对其中的一个Bucket的描述，上述数据字典中的字段ENDPOINT_VALUE记录了这些DISTINCT值，而字段ENDPOINT_NUMBER则记录了到此DISTINCT值为止总共有多少条记录。需要注意的是，对频率直方图而言，ENDPOINT_NUMBER是一个累加值，可以用一条记录的ENDPOINT_NUMBER值减去它上一条记录的ENDPOINT_NUMBER值来得到这条记录本身所对应的ENDPOINT_VALUE值的记录数，SQL如下所示：
1 2 3 4 | SELECT SAL,COUNT(*) COUNTS,SUM(COUNT(*)) OVER(ORDER BY SAL RANGE UNBOUNDED PRECEDING) CURR_ROWS FROM T_ST_20170604_LHR T GROUP BY T.SAL;--从表中获取累加值 SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,NVL((ENDPOINT_NUMBER-(LAG(ENDPOINT_NUMBER) OVER (ORDER BY ENDPOINT_VALUE))),ENDPOINT_NUMBER) COUNTS FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170604_LHR';--从直方图中获取分组记录数 |
（2）高度平衡（Height Balanced，HtBal）直方图
如果存储在数据字典里描述目标列直方图的Bucket的数量小于目标列的DISTINCT值的数量，那么这种类型的直方图就是高度平衡（Height Balanced）直方图。在高度平衡直方图中，执行计划的列的选择性没有频率直方图精确，而在现实很多时候，列的唯一值是超过254的，那么只能使用高度平衡直方图。在高度平衡直方图中，在DBA_TAB_HISTOGRAMS视图中，EDNPOINT_NUMBER代表桶号，且自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。ENDPOINT_VALUE表示每一个桶中的最大值，而第一个桶记录的是最小值（Bucket为0的行，即EDNPOINT_NUMBER为0的行）。重复出现为ENDPOINT_VALUE的值称为Popular Value。若Popular Value所在记录的ENDPOINT_NUMBER值和它上一条记录的ENDPOINT_NUMBER值之间的差值越大，则意味着该Popular Value在目标表中所占的比例也就越大，它所对应的Cardinality也就越大。在高度平衡直方图中，除了最后1个桶可能包含的数据比其它的桶少以外，所有其它的桶包含相同数量的值，其值为目标表总记录数除以Bucket的数量。
（3）频率和高度平衡直方图的比对
| 频率（Frequency,Freq）直方图 | 高度平衡（Height Balanced，HtBal）直方图 | |
|---|---|---|
| 简介 | 在目标列分布倾斜的情况下，目标列直方图的Bucket的数量（<=254）=目标列的DISTINCT值的数量。 | 目标列直方图的Bucket的数量<目标列的DISTINCT值的数量。 |
| 使用场合 | 频率直方图只适用于那些目标列的DISTINCT值数量小于或等于254的情形。需要注意的是，在Oracle 12c中，频率直方图所对应的Bucket的数量可以超过254。 | 在高度平衡直方图中，除了最后1个桶可能包含的数据比其它的桶少以外，所有其它的桶包含相同数量的值，其值为目标表总记录数除以Bucket的数量。 |
| ENDPOINT_VALUE的含义 | 若为数值类型则代表不同的DISTINCT值；若为字符类型则记录的是DISTINCT值转换后的值。 | 若为数值类型则表示每一个桶中的最大值，而第一个桶记录的是最小值（Bucket为0的行，即EDNPOINT_NUMBER为0的行）；若为字符类型则记录的是转换后的值。 |
| ENDPOINT_NUMBER的含义 | 记录了到此DISTINCT值为止累加的行数。 | 代表桶号，且自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值（节省空间，合并存储）。 |
收集直方图信息
默认情况下，数据库会为列收集基本统计信息，但不会收集直方图信息。Oracle通过指定DBMS_STATS的METHOD_OPT参数来创建直方图。METHOD_OPT参数可以接受如下的输入值：
1 2 3 | FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size_clause] column|attribute [size_clause] [,column|attribute [size_clause]...] |
其中的size_clause必须符合如下的格式：
1 | SIZE {integer | REPEAT | AUTO | SKEWONLY} |
含义如下所示：
SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。
REPEAT：只对己经有直方图统计信息的列收集直方图统计信息。
AUTO：让Oracle自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。
integer：直方图的Bucket的数量，必须是在1~254的范围内，1表示删除该目标列上的直方图统计信息。
METHOD_OPT参数的默认值为“FOR ALL COLUMNS SIZE AUTO”，“FOR ALL COLUMNS SIZE 1”表示删除所有列直方图统计信息。下面是一些常用的收集方法：
对T表上所有有索引的列以自动收集的方式收集直方图：FOR ALL INDEXED COLUMNS SIZE AUTO
对T表上的列A和列B以自动收集的方式收集直方图：FOR COLUMNS SIZE AUTO A B
对T表上的列A和列B收集直方图统计信息，同时指定BUCKET数量均为10：FOR COLUMNS SIZE 10 A B
对T表上的列A和列B收集直方图统计信息，同时指定列A的BUCKET数量为10，列B的BUCKET数量为5：FOR COLUMNS A SIZE 10 B SIZE 5
只删除表T上列A的直方图统计信息：FOR COLUMNS A SIZE 1
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
