增量分区统计信息收集的一点研究

Oracle在11g中能增量收集分区表的统计信息。在11g之前,收集global的统计信息时,oracle需要扫描每个分区的统计信息,才能得出global的统计信息。而在11g中,Oracle会简单记录分区的概要信息,(synopsis,记录在WRI$_OPTSTAT_SYNOPSIS_HEAD$ 和 WRI$_OPTSTAT_SYNOPSIS$中),对于global的统计信息,可以不再扫描每个分区的,只需通过计算概要信息,即可得出global的统计信息(可见下图示意)。

该设计的目的是通过分区的概要信息计算出global的信息,不再扫描各个分区。从oracle官方的例子中,给出的使用场景是,如sales表,为range分区,需要每天新增一个分区记录当天的销售状况。过去的分区几乎没有变动,主要的dml操作和select查询都会在新增的这个分区中。因此,当把sales的INCREMENTAL开启后(默认是不开启),当使用dbms_stats.gather_table_stats(,)收集统计信息时,由于其他分区几乎无变动,因此概要信息不会变动,只对一个分区的概要信息再次分析,并且通过概要信息,得出global的信息。

Oracle对满足如下条件的分区表采用增量的方式收集统计信息:

增量收集分区表的统计信息,在一定程度上能加快收集统计信息的时间,但是,这个方法也不是完全没有成本的。我们来分情况讨论:

(1)范围分区:
测试方法:
(a)先创建分区表test_inc和test_inc_2,其中设置test_inc表的incremental为true,test_inc_2表的incremental为false。
(b)初始化数据,给每个分区加载20万的数据。
(c)测试开始,测试insert 1个分区、8万行的数据量,insert完后,flush buffer_cache,计时收集统计信息;完成后,再insert 2个分区,4个分区……n个分区(n的值为分区表的分区数)
(d)重建分区表,重复上述测试,看针对不同的分区数量对收集效率的影响。

测试结果:

我们把上面的测试结果,从几个角度进行分析。
(a)Insert一个分区的数据量,随着分区数的增加,非Incremental方式收集的效率大大减少,而Incremental方式收集,只是随着数据量的增长,仅稍稍增长。因此,我们可以说对于range分区,且加载少量数据到少量的分区,incremental的效率要大大高于非incremental。

(b)我们看当分区数为64个,Insert 1个分区到Insert 64个分区的情况。随着Insert数据量的 增加,一开始Incremental方式收集很快,非Incremental的方式收集慢,但是在月56个分区数时(即到总分区数的87%左右),二者开始接近,到64个分区时,即insert了全部分区,Incremental的速度低于了非Incremental的方式。

(c)当Insert全部分区时,即表的分区数和Insert的分区数相等时,分区数越多,收集的速度越慢。当分区数为64个时,incremental方式收集为322.642秒,非incremental方式为290.364秒。也可以看到这期间的差距差距达到最大,差距为32.278秒。

综上,我们可以看出:范围分区表的incremental和非Incremental的方式收集,之间的效率差异主要一是在分区数,如果分区的数量越多,二者的差异越明显,incremental明显占优势;二是Insert的数据量所占的分区数,如果所占分区数越多,Incremental的优势会慢慢被非incremental赶超,最终,如果是insert的数据占用了全分区,incremental不如非incremental。

(2)哈希分区:
测试方法:
(2.1)先创建分区表test_inc和test_inc_2,其中设置test_inc表的incremental为true,test_inc_2表的incremental为false。
(2.2)初始化数据,给每个分区加载20万的数据。
(2.3)测试开始,测试insert 1个分区的数据,2个分区的数据,4个分区数据……(注:实际不是load到一个分区的,因为hash分区,insert数据会均匀分区,只是为了和range分区对比,range时是1个分区80万,load了一个80万的数据)
(2.4)重建分区表,重复上述测试,看针对不同的分区数量对收集效率的影响。

测试结果:

我们同样也从几个角度分析:
(a)我们看Insert 80万的数据量(即和range分区一样的一个分区的数据量),我们看到随着表分区数的增加,Incremental和非Incremental方式收集的速度都大大变慢,但非Incremental的方式始终是快于Incremental的方式。

(b)我们再来看表为64个分区的的情况。我们可以看到incremental和非incremental的收集方式都不快,都在100秒以上,随着Insert数据量的增加,二种方式的时间都在增加。 但在16个分区以上时,非incremental的时间长于incremental的时间。

(c)当Insert全部分区时,即表的分区数和Insert的分区数相等时,同样也可以看到Insert的数据越多,收集的速度越慢。

综上,我们可以看出:对于哈希分区表的incremental和非Incremental的方式收集,和range分区表的行为差异很大,并没有出现incremental很快,非incremental很慢的情况,且两种方式均随着数据量和分区数的增加,收集所消耗时间都增加。且基本是非incremental的方式快于incremental的方式。

从上面的两种分区情况,我们基本可以看出incremental的方式收集分区的统计信息比较适合于有多个分区,且变动分区比较少的分区,如range分区。而对于hash分区来说,由于没有范围界限,在理想状态下,数据是均匀分布到每个分区的,新load数据几乎平均load到每个分区,因此每个分区都是有变动的。而变动的分区,在增量分区统计信息时,是需要重新计算概要信息的(会大量dml WRI$_OPTSTAT_SYNOPSIS$),因此,hash分区是几乎每个分区都要做WRI$_OPTSTAT_SYNOPSIS$的dml的。

我们可以通过10064的trace来看看incremental和非incremental之间的区别,我们再次测试64个hash分区的表中load 32万的数据的场景,并且做trace。
我们看到,无论是incremental还是非incremental的方式,一开始都是统计table变动的数据,如insert col_usage$ ,lock mon_mods$和mon_mods_all$等等。
在后续的步骤中,出现了差异:incremental的方式会做针对数据量变动的操作,会delete wri$_optstat_synopsis_head$表和WRI$_OPTSTAT_SYNOPSIS$表,而非incremental的方式则没有这些步骤:

上面的这几个步骤,在非incremental中的trace file是没有的,

另外,我们可以在trace file中,可以进一步看到,语句DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ S WHERE S.BO# = :B2 AND S.GROUP# = :B1 * 2,是耗时第二多的步骤,为20.24秒

花费第一耗时的步骤为

但是请注意,这个带where条件的select分区表的步骤,无论是在incremental还是在非incremental中,都是存在的,Execute count 是64
我们可以把trace file用文本比较工具进行对比,可以看到这样的操作,incremental和非incremental的方式都是有这个步骤的操作的,都是是走全表扫描。做的次数等于分区数。

在非incremental中的trace,我们可以看到同样的语句:

另外,我们对比非incremental的trace文件,我们还能看到,对已非increment的方式来说,除了上面的语句,另外还有一个语句也是非常消耗时间。这一步就是少了条件where条件的上面的select分区表语句,是整个分区的扫描。

这一步是在非incremental中最耗时的一步。

因此综上,我们从2个最消耗时间的步骤来看,
incremental的主要耗时=DML WRI$_OPTSTAT_SYNOPSIS$ + select 分区表 where TBL$OR$IDX$PART$NUM(“TEST”.”TEST_INC”,0,4,0, “ROWID”) = :objn
非incremental的主要耗时= select 分区表全部分区 + select 分区表 where TBL$OR$IDX$PART$NUM(“TEST”.”TEST_INC”,0,4,0, “ROWID”) = :objn

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据