一个执行计划异常变更的案例 – 外传之直方图

感谢你们在这一年当中对”bisal的个人杂货铺”的关注和指点，来年我们共同继续努力！今天单位值班，有一些时间可以继续完成这篇连载文章，迎接新年的这一天，完成工作的同时，学习了下直方图，整理出一篇文章，也颇有意义。首先祝所有朋友新年快乐！

之前的几篇文章：  
《一个执行计划异常变更的案例 - 前传》  
《一个执行计划异常变更的案例 - 外传之绑定变量窥探》  
《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》  
《一个执行计划异常变更的案例 - 外传之rolling invalidation》  
《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》  
《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》  
《一个执行计划异常变更的案例 - 外传之AWR》  
《一个执行计划异常变更的案例 - 外传之ASH》
《一个执行计划异常变更的案例 - 外传之SQL AWR》

这篇文章我们聊聊直方图。

首先我们看下统计学中对直方图的定义：

直方图(Histogram)又称质量分布图。是一种统计报告图，由一系列高度不等的纵向条纹或线段表示数据分布的情况。 一般用横轴表示数据类型，纵轴表示分布情况。

可以看出，直方图可以用来描述数据分布的情况。Oracle中也是如此，直方图可以准确预测列数据的分布，尤其在出现数据分布倾斜的情况下，通过直方图信息，可以选择最优的执行计划。

P.S. 关于直方图的介绍，推荐dbsnake的书，其中第五章详细介绍了11g的直方图，非常详细受用。

11g下有两种类型的直方图(12c又多了其他类型的直方图)：
Height-Balanced Histograms
Frequency Histograms

查询USER/DBA_TAB_COL_STATISTICS视图的HISTGRAM列可以知道存储的是何种类型的直方图(取值为HEIGHT BALANCED,FREQUENCY,NONE)。

创建测试表，name列有100000行值为A，1行值为B，数据出现了倾斜，name列存在非唯一二叉树索引，采集统计信息时不收集直方图，

从HISTGRAM列可以看出未有任何直方图统计，

根据name=’A’检索，选择了全表扫描的执行计划，

根据name=’B’检索，同样选择了全表扫描的执行计划，

从数据分布看，A的记录有100000条，B的记录有1条，该列有索引，按说A为条件的SQL应该选择全表扫描采用多块读的方式最高效，B为条件的SQL应该使用索引采用索引扫描的方式最高效，但实际情况是两者均采用了全表扫描的执行计划。

原因就是此时Oracle认为name列值是均匀分布的，根据Cardinality的计算，

Computed Cardinality = Original Cardinality * Selectivity  Selectivity= 1 / NUM_DISTINCT

计算如下：

Computed Cardinality = 100001 * 1 / 2

约等于50001，可以从上面两个执行计划中Rows预估行看出两个SQL的预估行均为50001。

接着我们收集name列的直方图，此处未指定method_opt会由Oracle自行来判断收集的直方图信息和类型，

可以看出name列采集了FREQUENCY类型的直方图信息，

我们再执行刚才的两条SQL，name=’A’的仍选择了全表扫描，我们要重点看下B的SQL，此时选择了索引范围扫描，不是全表扫描了，说明Oracle知道了这列的数据分布，CBO认为索引扫描成本值更低，从10053事件可以查看具体计算值，

但有一处要注意，就是Rows这是18，我们之前知道name=’B’只有1条记录，怀疑这和采用默认的统计信息收集比率有关，默认未必采用了100%的数据作为样本，重新以100%的比例采集统计信息，

可以看出Rows是1了，说明Oracle此时已经知道了数据的分布，CBO计算时知道使用索引扫描成本值更低了。

Oracle直方图使用一种称为Bucket(桶)的方式来描述列的数据分布，每个Bucket就是一组，其中会存储一个或多个列数 据，Bucket使用ENDPOINT NUMBER和ENDPOINT VALUE两个维度来描述，其中ENDPOINT VALUE记录列的distinct值，ENDPOINT NUMBER表示到此distinct值为止总计有多少条记录(即这条distinct值对应的ENDPOINT NUMBER减上条记录distinct值对应的ENDPOINT NUMBER就会是这条distinct值的记录数)，上面示例中name列是FREQUENCY类型的直方图，对于这种类型的直方图，Bucket的数 量就是列distinct值的数量，从NUM_DISTINCT知道有2个distinct值，

因此user_tab_histograms中列name对应的记录(Bucket)应该是2条，

我们看出第一条记录：
ENDPOINT VALUE：337499295804764000000000000000000000 

ENDPOINT NUMBER：100000
第二条记录：
ENDPOINT VALUE：342691592663299000000000000000000000
ENDPOINT NUMBER：100001

上面说ENDPOINT VALUE是distinct值，我们看下如何推导出，以A为例，A对应的十六进制是0x41，将0x41右补至15个字节长度的0，再将其转换为十进制，即3.3750E+35，正如上面对应的第一条记录ENDPOINT VALUE值，

第一条记录的ENDPOINT NUMBER是100000，说明有100000条记录值是A，第二条记录的ENDPOINT NUMBER是100001，说明有(100001-10000=1)条记录值是B。

对于这种FREQUENCY的直方图，dbsnake书中明确说明了其缺点，就是适合于一些distinct值少的情况，因为11g的 FREQUENCY直方图对应的Bucket数量不能超过254(12c不受此限制)，如果列值distinct值超过254，则不能使用这种类型的直方 图。而且若列值类型是文本型，采集直方图时只会采集文本值头32个字节，换句话说，若多个列值distinct的头32个字节相同，则Oracle可能会 将他们作为一个值来采集，就会对采集结果产生影响，这是错误。

对于列值distinct超过254的情况，Oracle会采集HEIGHT BALANCED类型的直方图。这种类型的直方图首先会根据列的所有记录按从小到大的顺序排序，用总记录数处于需要使用的Bucket数量，决定每一个 Bucket中要存储的记录数，对于相邻Bucket的仅ENDPOINT NUMBER不同，ENDPOINT VALUE值相同记录数做合并存储，ENDPOINT VALUE存储的是到此记录所描述的Bucket为止之前所有Bucket描述的记录中列的最大值，通过实验我们体会下，创建测试数据，

name列有301个distinct值，其中值为201有700条记录，采集统计信息时指定Bucket数量是10，此时查看user_tab_col_statistics的HISTOGRAM值变为了HEIGHT BALANCED，

select 1000/10 from dual;

知道每一个Bucket应该存储100条记录数，

0号Bucket存储的是列最小值，即1，1-10号Bucket存储的是到此记录所描述的Bucket位置之前所有Bucket描述的记录中列的最大 值，每个Bucket存储100条记录数，因此这可以推测出1号Bucket的ENDPOINT VALUE是之前存储的最大值100，ENDPOINT NUMBER是1-0=1，因为每一个distinct这只有一条，值为201的记录有700条，一个Bucket不足以存储，需要7个Bucket，从 顺序上看，是2号至9号，由于这几个Bucket的ENDPOINT NUMBER不同，ENDPOINT VALUE值相同，因此做了合并，这种合并后的ENDPOINT VALUE称为popular value，该值记录的ENDPOINT NUMBER和上一记录的ENDPOINT NUMBER差值越大，则意味着这个popular value在表中所占比例也就越大，对应的Cardinality就越大了，进而影响执行计划的成本计算。

此时我们根据name=201执行，选择了全表扫描的执行计划，

根据name=1执行，此时选择了索引扫描的执行计划， 

对于这种distinctr超过254的情况，HEIGHT BALANCED用这种方式存储了直方图信息，计算成本时参考，因此选择了正确的执行计划。

总结：
直方图描述了列的数据分布情况，对于列值数据分布倾斜的表，使用直方图可以帮助选择正确的执行计划，11g有两种直方图类型，FREQUENCY和 HEIGHT BALANCED，其中FREQUENCY适合于distinct不超过254的表，而且有错误预测的可能。HEIGHT BALANCED采用这种popular value的合并方式来存储直方图信息且对执行计划Cardinality的预测提供参考依据。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！