在oracle 11g中auto_sample_size是如何工作的?
当要准备收集统计信息时,一个最重要的决定是你将使用什么样的抽样大小.一个100%的抽样大小能确保生成准确的统计数据但是它可能要收集很长时间.如是执行1%的抽样将会快速完成收集但是可能会生产不准确的统计数据.
在dbms_stats.gather_*_stats过程中estimate_percent参数当收集统计信息时控制着抽样大小,而且它的缺省值是auto_sample_size
首先来看看auto抽样大小在oracle 11g中的增强
oracle管理统计信息是通过pl/sql包dbms_stats来管理的.dbms_stats包提供了一些pl/sql过程来对表,方案或数据库收集统计信息.这些过程有一个estimate_percent参数,它用来指定收集统计信息时抽样大小的百分比.用户可以指定0到100的任何数字.例如,有一个表TEST,可以对它指定1%的抽样百分比:
exec dbms_stats.gather_table_stats(user,'TEST',estimate_percent => 1);
用户要指定一个合适的抽样百分比是不容易的.如果你指定的抽样百分比太高,那么收集统计信息会花费很长的时间.相反如果数据极端的倾斜且指定的抽样大小太低,那么生成的统计信息可能是不准确的.由于这个原因,oracle对estimate_percent参数引入用auto抽样大小.例如,可以对表TEST指定auto抽样大小:
exec dbms_stats.gather_table_stats(null,'TEST',estimate_percent => dbms_stats.auto_sample_size);
使用auto抽样大小比使用固定的抽样大小有两个优势.第一,当指定auto抽样大小时,系统会自动判断一个合适的抽样百分比.第二,auto抽样大小与固定的抽样大小更灵活.一个固定的抽样百分比在有些时候是好的,但是表的数据分布发生变化后可能就不合适了.换句话说当auto值被使用时当数据分布发生改变后oracle将会自动调整抽样大小.
当oracle使用auto抽样大小来让oracle选择一个合适的抽样大小时生成的统计信息是足够准确的.然而,它在数据极端倾斜的情况下收集统计信息是不准确的.在oracle11g中,当使用auto抽样时已经改变了它的行为.第一,auto抽样现在能生成确定性的统计信息.第二也是更重要的是,auto抽样生成的统计信息与100%抽样生成的统计信息几乎是一样的准确但是auto抽样比100%抽样花费的时间要少.下面做一个测试比较使用固定抽样大小的性能,和在oracle10g和oracle11g中比较auto抽样的情况.我们收集的表名为KCR5,表大小有35G,627228900行.
desc kcr5 Name Type ------ ------------ AKB020 VARCHAR2(20) AAZ218 VARCHAR2(20) PKA001 NUMBER(5) PKA438 VARCHAR2(1) PKA435 VARCHAR2(30) AAE100 VARCHAR2(1) PKA439 VARCHAR2(20) Y PKA044 VARCHAR2(1) Y
下面的表格给出了不同抽样百分比收集统计信息的时间
抽样百分比 运行时间(秒)
1%抽样大小 154
100%抽样大小 3404
oracle10g的auto抽样大小 503
oracle11g的auto抽样大小 356
对35G的表KCR5使用不同抽样百分比收集统计后可以比较收集统计信息的质量.在一个列的所有统计数据中,不重复值的数量的准确性以前是一个问题.列的不重复值的准确率的计算公式定义如下:
accuracy rate=1-(estimated ndv -actual ndv)/actual ndv.
这个accuracy rate准确率从0%到100%.这个准确率越高,收集的统计信息越准确.因此100%的抽样的准确率总是100%.我们不用关注准确率100的数据,只要关注准确率小于99.9%的下面的是使用不同抽样百分比抽样的数据
列名 实际不重复值数量 11g中的auto抽样 1%抽样
AKB020 34000000 98.3% 49.7%
PKA001 12048687 98.7% 23.4%
PKA438 7000458 99.1% 98.4%
PKA435 5084956 99.5% 99.3%
PKA439 3075965 99.6% 99.4%
从上面的信息可以知道,在oracle11g中使用auto抽样大小的收集时间只有使用100%抽样大小的十分之一,但是收集的统计信息准确率是接近的.
在oracle11g中使用auto_sample_size收集统计信息时收集时间和准确性与oracle10g相比都有提高.
这里我们主要是讨论一个与oracle11g中新auto_sample-size算法相近的算法和这个算法是如何影响收集统计信息的准确性的.
在研究新的收集算法之前,先来看一下旧的算法:
第一步:oracle在开始收集统计信息时使用一个较小的抽样百分比,如果有直方图需要被收集,oracle可能会根据抽样的百分比物化这个抽样
第二步:oracle收集基本列的统计信息样本时.例如,表T只有一个列c1,那么基本的统计收集查询语句就类似下面的(它不是一个真实的语法)
select count(*),count(c1),count(distinct c1),sum(sys_op_opnsize(c1)),min(c1),max(c1) from T sample(x.0000000000);
查询是在oracle10G中使用auto_sample_size来收集基本的列的统计信息.这个查询的select列表中的项目对应查询表t中的行数,不为null值的记录数,不重复值的记录数,总的列长,C1列的最小值和最大值.在from子句中的”x.0000000000″由于oracle决定的抽样百分比.
第三步:如果直方图需要收集,oracle会对每一个请求直方图的列使用sql查询来抽样.
第四步:对于每个列要求直方图时oracle使用几个指标来判断当前抽样是否满足要求.
非重复值指标:对于这个列抽取的样品中是否包含了足够的非重复值
重复值指标:重复值的数量是否能够适当的从抽到的样品是进行扩展
第五步:如果在第四步中的所有指标都通过了,oracle认为当前的抽样大小是足够的且会对列完成直方图的创建.否则会认为抽样大小不够要增加抽样大小且重复上而后步骤直到找到一个满足条件的抽样大小或接近100%的抽样大小.
注意第三步到第五步对于每一个列都要进行.例如,如果表中有3个列请求创建直方图,在第一次迭代中我们得到一个样本并物化它,我们会使用3个查询,每个列一个,在相同的物化样本中收集直方图信息.假设oracle认为抽样大小对于第一列和第二列是足够的但对于第三列是不够的,那么会增加抽样大小.在第二次迭代中只有一个查询在修改抽样大小后的样品中对第三列收集直方图.
就如我们看到的如果有几次迭代被请求时旧的auto_sample_size可能会失效.几次迭代的主要原因是不能使用小的抽样来收集真实的重复值的数量.如果数据有倾斜,那么大量的低频率的值不会被抽取到样品中因为对于重复值指标来说抽样是失败的.
在oracle11g中我们对于基本列统计使用完不同的收集方法.我们使用下面的查询来收集列基本的列统计
select count(c1),min(c1),max(c1) from T;
查询是在oracle11g中使用auto_sample_size选项收集基本列统计信息的查询.注意在新的基本列统计收集查询中,没有抽样子句被使用.替代它的是执行一个全表扫描.所以这里没有count(distinct c1)来收集c1的重复值数量,相反当执行这个查询时会注入特殊的统计信息收集行资源.这个特殊的收集行资源使用一次通过基于哈希的不重复算法来收集重复值的信息.这个算法要求完全扫描数据,使用有限期数量的内存来生成高度精确的重复值数据与100%抽样几乎接近.这种特殊统计收集行资源的方法也收集行的数量,null值的数量和列的平均长度.因为对表执行了完全扫描,行的数量,列的平均长度,最小值和最大值都是100%的准确.
auto_sample_size也会影响直方图和索引统计信息的收集
auto_sample_size对直方图收集的影响
使用新的auto_sample_size算法时,直方图的收集是脱离基本列统计收集的(它们以前是在相同的抽样样品中进行收集的).因此当判断我们是否要增加抽样大小时,新的auto_sample_size算法不再执行重复值指标检查,因为不能从这个样品中得到重复值.对于直方图来说只有当抽样样品包含太多的null值或太少的行源时才需要增加抽样大小.这能够减少创建直方图所需要的迭代次数.
如果最小(或最大)值出现在用于收集直方图的样品中它不是在基本统计信息中被收集的最小(或最大)值,将会修改直方图因此在基本统计中收集的最小(或最大)值在直方图中会作为最一个(或最后一个)桶的端点而出现.
auto_sample_size对索引统计收集的影响
新的auto_sample_size算法也会影响索引统计信息的收集.索引统计信息收集是抽样的基础.它可能要经过几次迭代因为它要么包含太少的数据块要么为了收集重复键值抽样的大小太小.使用新的auto_sample_size算法,如果这个索引定义在一个单列上,或者索引定义在多列(一组列)上,那么列或列组的重复值将会被用作索引的重复键.那么在这种情况下索引统计收集查询将不会再收集重复键.这有助于减少因为索引统计收集而要增加抽样大小的成本.
小结:
1.新的auto_sample_size算法收集基本列统计时执行全表扫描
2.通过新的auto_sample_size收集重复列值与100%抽样大小收集有一样的准确率
3.其它的基本列统计象null值的数量,列的平均长度,最小和最大值与100%抽样大小收集有相同的准确率
4.基于新的auto_sample_size算法,直方图和索引统计收集仍使用抽样,但是新的auto_sample_size算法有助于缓解增加抽样的样本量.
参考
https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size