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

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