Rebuild index 对DML的影响

作者简介:张立,现就职于北京海天起点,获有12c OCP、MYSQL OCP认证,具有多年oracle数据库和goldengate管理维护经验,并且对于mysql的主从搭建、升级、日常维护也具有丰富实践经验。主要从事客户的现场维护,服务对象覆盖电信、国家税务局等大型企事业单位,擅长数据库故障分析,性能调优等。

索引对于系统性能的提升有着显著的效果,但是由于系统自身对索引维护的局限性,我们不得不手动维护索引,重建索引就是最重要的维护手段。

很多业务无脑的重建索引导致的惨案数不胜数,下面我们就来深入的了解一下重建索引对基表dml的影响。Oracle为我们提供了alter index rebuild 来重建索引,我们可以选择添加online参数来在线重建索引。

普通的rebuild index和rebuild index online的主要区别在于是否阻塞dml操作。普通的rebuild index是通过index fast full scan(or table full scan,取决于CBO对cost的预估) 方式读取原索引中的数据来构建一个新的索引,它使用原索引的叶子节点作为新索引的数据来源,所以重建操作会比较快,但是此时oracle会对基表加share锁,由于share锁和 row-X是不兼容的,也就是说,在建立索引期间,无法对基表进行DML操作。 Rebuild index online重建索引期间会先执行 TABLE ACCESS FULL获取数据,也有排序的操作这就是为什么rebuild online会比rebuild时间长一些; 同时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表(类似于物化视图日志),所有 rebuild online 时索引的变化都记录在这个表中,可以实现重建期间不阻塞DML操作,当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后 drop 掉旧的索引,rebuild online 就完成了。

Oracle对于两种方式重建索引解释的执行计划

rebuild index:


rebuild index online:


既然rebuild index online期间允许DML操作,为什么很多重建索引的操作还是会给数据库造成很大压力,甚至导致宕机呢?其实rebuild index online在重建期间不会阻塞dml操作,但是在重建索引的开始和结束都会请求一个4级表锁,如果请求产生等待就会导致一些问题了。

以下是在10.2.0.5上进行的相关测试过程:

模拟表上有DML长事物未提交情况下在线重建索引

会话一:更新一条数据但是不提交



会话二:在线重建索引(注意并行重建索引会导致索引的并行度改变)



此时重建索引的会话挂起,表并不是太大,索引重建却迟迟没能完成,肯定是遇到了问题,此时查看session2的相关信息

果然,此时会话二被阻塞了,阻塞会话的sid=2939,等待事件是TM队列锁

再查看一下数据库的锁信息

此时sid=2939也就是会话一持有对象id 196212(test3)的3级表锁,而会话二(sid=2954)正在排队请求获得test3的4级表锁,由于无法获得请求的锁所以产生了长时间的等待。

那如果我们再发起一个dml操作会有什么后果呢

会话3:



再次发起的dml操作也挂起了,查询相关信息



原来是session3在请求获得test3的3级表锁无法获得,导致dml挂起,如果这是dml操作比较频繁的生产系统那后果可想而知。此时我们回滚掉阻塞rebuild index online 的session1操作,session3的dml操作立刻完成,再次查看数据库的锁信息

可以看到此时session2持有的是test3表的2级锁,不影响表上的dml操作,由此看来刚才session2请求获得的4级表锁只是短暂的持有,online重建索引期间确实可以进行dml操作

查询重建期间的数据库对象,会有一个临时的索引和日志表(如果是大表重建可能会临时占用较大的空间)


我们不提交session3的dml操作,发现session2还是迟迟不能结束操作

查询session和lock信息发现session2又开始请求test3的4级表锁,此时如果我们发起dml操作肯定还是会被阻塞。


回滚会话三的更新,索引重建很快完成


从测试结果来看,rebuild index online期间确实不影响dml操作,但是在重建开始和结束的时候都会请求短暂持有表的4级锁,如果获得不了就会阻塞后续所有的dml操作。庆幸的是oracle已经在11g版本中优化了在线重建索引,11g里如果表上有dml的长事物,rebuild online仍然会产生长时间的等待,但是并不会阻碍后续的dml操作,这里就不做详细的测试了,但是不管在哪个版本,当重建索引长时间不结束时,都不能轻易取消掉重建操作,随意的取消rebuild index命令可能会导致ora-08104(具体解决方法可以参考mos文档 ID 375856.1),最好是找到阻塞的会话,释放相关的资源锁。

对于rebuild index的一些小建议:

  1. 尽量在没有DML操作期间使用rebuild index重建索引
  2. 使用rebuild index online前要先确认表上没有dml的长事物
  3. 不要轻易结束重建索引操作,可以杀掉阻塞的会话
  4. 大表Rebuild index online期间会临时占用大量空间,需要关注表空间的使用率


未经允许不得转载:Oracle一体机用户组 » Rebuild index 对DML的影响

相关推荐