删除分区缓慢的一次分析

在客户处遇到个问题,客户昨晚清理数据,删除一些表的分区,第一个表跑了20多分钟,但是差不多数据量的另外一个表,跑了好几个小时才完成。为何类似的数据在drop 分区的时候,有那么大的差异?

检查了改删除分区的语句,发现是update了global索引,并且,还用到了parallel 8的并行。

虽然时间已经过去了一个晚上,但是根据当晚操作人员提供的信息,结合在测试库上的测试,我们还是能从已知的信息中获取一些可能导致当时运行缓慢的一些线索:
(1) 从测试库的测试看到,正常执行是有并发的PX类型的等待的。
(2) 从DBA反馈得知,当晚没有看到并发子进程,只看到单个进程。
(3) 测试有并发和没并发的drop分区,在10046的trace中行为有很大不同,有并发的主要是PX、direct path read、db file scattered read等待,没并发主要是db file sequential read等待。
(4) 设置parallel max servers为4,先运行4个并发的脚本占据并发度,再运行并发drop分区的脚本,从10046的trace看,后面执行的并发drop分区没有并发子进程,且大部分是db file sequential read等待。

执行上述的drop partition时,我们能看到相关并发进程的等待事件:

从上面,我们可以看出,脚本在执行的时候,应该是会有并发进程出现,如果按照username=’MYTEST’查询,应该可以看得到PX的session等待。但是和当晚操作人员的沟通中得知,当天晚上,他观察到的现象是没有看到并发的进程,只看到一个进程长时间处于db file sequential read的等待,该进程是在执行4094517213的语句。
从当晚的V$SESSION_WAIT snapshot记录进一步检查, 检查db file sequential read所对应的file id和block id,都是在操作PK_MY_TEST_TABLE_OWN_BY_ORA:

另外,我在我的测试库中测试了用并发和不用并发,drop 500万数据的partition:

再结合我们V$SESSION_WAIT snapshot记录,我们看到,语句发起时,开始的行为吻合并发时的行为,而后面从1:58分之后,行为就变成了像拿不到并发,变成了串行的执行,于是出现大量的db file sequential read的等待。

……

因此从DBA反馈的当晚的情况看,和测试的现象看,应该是当晚执行的时候,没有拿到并发,导致语句虽然带有并发参数,但是实际执行的时候没有并发。由于串行,所以执行的时间很长。

至于为什么没有拿到并行,我们目前对于9i库没有更多的信息来证明这个问题,没有session hist的记录或者别的记录。但是根据以往的经验,当数据库中有多个程序都是并发执行时,抢光了parallel_max_servers,后续有并发的进程就不能用并发进程。且哪怕占用并发度的进程结束后,没有拿到并发度的进程还在执行,此时依旧还是会按照串行的方式继续进行下去,一直到sql执行结束。

因此,我测试了设置paralle max server为4,先用并发度为4的drop partition语句占用这4个并发度,再运行并发度为8的语句drop 另外一个表的partition,后面运行的这个sql在拿不到并发度之后,会和我们当晚的情况非常一致,会长期处于db file sequential read的等待:

建议:
1. 目前该数据库的并发度为48(parallel_max_servers = 48),可适当加大。
2. 拿不到并发度,可能是在凌晨12点左右有大量job或者其他占用并发的作业启动,建议错峰执行,如提早2小时执行。
3. 执行前判定剩余并发度,如果不能获得足够的并发度,就暂缓执行。

相关文章

发表评论

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.