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个人页面-阿里云开发者社区

个人介绍

擅长的技术

创建索引的两种方式比对

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

强制找回GitLab管理员账户密码的方法

一个执行计划异常变更的案例 - 外传之ASH

一个执行计划异常变更的案例 - 外传之AWR

我的CSDN博客

CC2的评价与自我感受

在WinMe中安装java环境

JSP连接数据库大全

初学ASP.NET

SCJP备考复习。。。

Flexsim

SCJP,新的起点

ASP.NET一点点心得

Eclipse快捷键-方便查找，呵呵，记性不好

Tomcat5.5 Administration Web Application配置

Automony公司的UltraSeek产品

windows正版验证问题。。。

MySQL的导入导出问题。。。求解。。。

Win Me下的Ghost

SQL Server的六种数据移动方法

项目问题

Java环境变量批处理文件设置问题

利用本体形成最佳搜索字符串问题

开发备份