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

加入dbDao.com Oracle技术学习QQ群:171092051

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 400-690-3643 备用电话: 13764045638 邮箱:service@parnassusdata.com

 

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对满足如下条件的分区表采用增量的方式收集统计信息:

•    The INCREMENTAL value for the partitioned table is true.
•    The PUBLISH value for the partitioned table is true.
•    The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

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

(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的方式则没有这些步骤:

……
select tbl$or$idx$part$num(sys.wri$_optstat_synopsis$, 0, 4, 65535, 17016) from dual             
--                                                                                               
LOCK TABLE "SYS"."WRI$_OPTSTAT_SYNOPSIS$" PARTITION ("P_17015")  IN EXCLUSIVE MODE  NOWAIT       
--                                                                                               
 alter table sys.wri$_opt                                                                       
--                                                                                               
 delete from sys.wri$_optstat_synopsis_head$ h where h.bo# = 17016                               
……
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ S WHERE S.BO# = :B2 AND S.GROUP# = :B1 * 2
……

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

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

[oracle11g@testdb2 trace]$ grep total ora11g_ora_2563.txt |awk '{print $4}' |sort -rn |more
136.70
<<<总的
103.81
20.24
4.81
……
 
SQL ID: ct3ch355nvhzm Plan Hash: 3506642473
 
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ S
WHERE
 
S.BO# = :B2 AND S.GROUP# = :B1 * 2
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     64     11.72      20.24       8346      20618    1180812     1124800
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       65     11.72      20.24       8346      20618    1180812     1124800

花费第一耗时的步骤为

SQL ID: dh7bk0cprns3f Plan Hash: 3475122680
 
select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_substrb_pad  */to_char(count("A")),to_char(substrb(dump(min("A"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("A"),16,0,32),1,120)),to_char(count("B")),
  to_char(substrb(dump(min("B"),16,0,32),1,120)),to_char(substrb(dump(max("B")
  ,16,0,32),1,120)),to_char(count("C")),to_char(substrb(dump(min("C"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("C"),16,0,32),1,120)),to_char(count("D")),
  to_char(substrb(dump(min("D"),16,0,32),1,120)),to_char(substrb(dump(max("D")
  ,16,0,32),1,120)),to_char(count("E")),to_char(substrb(dump(min("E"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("E"),16,0,32),1,120)),
  to_char(count("PARTITION_KEY")),to_char(substrb(dump(min("PARTITION_KEY"),
  16,0,32),1,120)),to_char(substrb(dump(max("PARTITION_KEY"),16,0,32),1,120))
from
 "TEST"."TEST_INC" t  where TBL$OR$IDX$PART$NUM("TEST"."TEST_INC",0,4,0,
  "ROWID") = :objn /* SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,
  NIL,SYN,NIL,NIL, B17428, C1, C1, C1, C2, C2, C2, C3, C3, C3, C4, C4, C4, C5,
   C5, C5, C6, C6, C6*/
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       64      0.00       0.00          0          0          2           0
Execute     64      0.00       0.02          0          0          0           0
Fetch       64     13.00     103.78     312558     312812         31          64
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      192     13.01     103.81     312558     312812         33          64

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

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

SQL ID: dgypdg97zvssx Plan Hash: 3142812646
 
select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_substrb_pad  */to_char(count("A")),to_char(substrb(dump(min("A"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("A"),16,0,32),1,120)),to_char(count("B")),
  to_char(substrb(dump(min("B"),16,0,32),1,120)),to_char(substrb(dump(max("B")
  ,16,0,32),1,120)),to_char(count("C")),to_char(substrb(dump(min("C"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("C"),16,0,32),1,120)),to_char(count("D")),
  to_char(substrb(dump(min("D"),16,0,32),1,120)),to_char(substrb(dump(max("D")
  ,16,0,32),1,120)),to_char(count("E")),to_char(substrb(dump(min("E"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("E"),16,0,32),1,120)),
  to_char(count("PARTITION_KEY")),to_char(substrb(dump(min("PARTITION_KEY"),
  16,0,32),1,120)),to_char(substrb(dump(max("PARTITION_KEY"),16,0,32),1,120))
from
 "TEST"."TEST_INC_2" t  where TBL$OR$IDX$PART$NUM("TEST"."TEST_INC_2",0,4,0,
  "ROWID") = :objn /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,
  NIL,NDV,NIL,NIL*/
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       64      0.00       0.00          0          0          0           0
Execute     64      0.00       0.00          0          0          0           0
Fetch       64     13.47      65.02     312509     318578         30          64
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      192     13.47      65.03     312509     318578         30          64

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

SQL ID: 77kvs3t5zqvrh Plan Hash: 2116658917
 
select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_substrb_pad  */to_char(count("A")),to_char(substrb(dump(min("A"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("A"),16,0,32),1,120)),to_char(count("B")),
  to_char(substrb(dump(min("B"),16,0,32),1,120)),to_char(substrb(dump(max("B")
  ,16,0,32),1,120)),to_char(count("C")),to_char(substrb(dump(min("C"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("C"),16,0,32),1,120)),to_char(count("D")),
  to_char(substrb(dump(min("D"),16,0,32),1,120)),to_char(substrb(dump(max("D")
  ,16,0,32),1,120)),to_char(count("E")),to_char(substrb(dump(min("E"),16,0,32)
  ,1,120)),to_char(substrb(dump(max("E"),16,0,32),1,120)),
  to_char(count("PARTITION_KEY")),to_char(substrb(dump(min("PARTITION_KEY"),
  16,0,32),1,120)),to_char(substrb(dump(max("PARTITION_KEY"),16,0,32),1,120))
from
 "TEST"."TEST_INC_2" t  /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,
  NDV,NIL,NIL,NDV,NIL,NIL*/  --注意,这里没有条件 where TBL$OR$IDX$PART$NUM("TEST"."TEST_INC",0,4,0,  "ROWID") = :objn,是全分区扫描

这一步是在非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

(1)当使用哈希分区时,加号后一部分的时间大致相同,基本都会扫描到所有的分区,因此,差异在加号前面的半部分,如果全表扫描所有分区的效率大于DML WRI$_OPTSTAT_SYNOPSIS$的效率,那么非incremental就快。反之,incremental就慢。在测试中,对于64个分区的hash表来说,在原有1280万数据的基础上,变动了(Insert了)32万的数据,变动的数据比较多,因此做DML WRI$_OPTSTAT_SYNOPSIS$比较多,因此采用incremental的方式要慢于非incremental的方式。
(2)当是用范围分区时,做DML WRI$_OPTSTAT_SYNOPSIS$很少,因此incremental的方式能快于非incremental的方式。
(3)从目前的测试情况看,对于hash分区来说,非incremental的方式在大部分情况都是快于incremental的方式;对于range分区说,非incremental的方式在修改分区数据量超过总分区数87%以上的时候,快于incremental的方式。

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569