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

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 外传之直方图

感谢你们在这一年当中对”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的个人杂货铺,您的支持是对我最大的鼓励!

一个执行计划异常变更的案例 - 外传之直方图
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 外传之直方图


 上一篇
Oracle 12c CC安装部署攻略 (上) Oracle 12c CC安装部署攻略 (上)
整篇文章保存报错内存溢出,所以只能分为上下两部分发布了。 之前统一管理非生产数据库的Oracle 11g GC(Grid Co)环境所用虚机被破坏了,导致无法访问,干脆安装CC(Cloud Control)新环境,现在Oracle提供了12
下一篇 
Oracle 12c CC安装部署攻略 (下) Oracle 12c CC安装部署攻略 (下)
《Oracle 12c CC安装部署攻略 (上)》请从历史文章中检索,本篇是Oracle 12c CC安装部署的下半部分。 四、登录验证 CC版本不同,默认使用的端口可能不同, 五、安装部署Agent 11g