程序媛记一次在线创建索引被kill案例及应对措施

作者 | 李晴晴，云和恩墨西区技术顾问，有多年数据库服务经验，主要服务于电网、政府、银行等行业客户，擅长SQL审核、性能优化、数据库升级。

在日常的数据库优化工作中，发现某sql执行效率较低，并且运行频率较高。查看其执行计划发现走全表扫描，而sql的谓词条件是对某一number类型列进行精确匹配，而该列的选择性较好，故建议厂商运维人员在该列上创建索引，来优化sql的效率。

相关运维人员在和开发人员确认可行性后，通过PLSQL Developer客户端在生产环境执行创建索引操作，报ora-00054错误，见下图（部分敏感信息已打码）

然后在创建索引语句后加上了online关键词，继续在PLSQL Developer客户端执行，但一直没有返回结果，故联系我看下原因。

一般，在出现ora-00054报错时，主要原因就可能是该表存在尚未commit或rollback的DML命令，而在线创建索引没有返回的原因也应该是被别的会话进程阻塞了，有这个思路就去验证，查看当前系统的锁便发现：

查看 holder 的 sql_id 文本：

而 waiter 的确实是创建索引的进程： 

确实存在进程对该表进行 insert 操作，而这个进程一直未提交，咨询相关人员表示这个进程存在缓存数据，由于业务关系不能将这个进程 commit 或者 kill。于是运维人员就把创建索引这个进程 kill 掉了。

原以为杀了创建索引的进程就能“好”了，却发现事实远远不是这样。

查看dba_indexes和dba_ind_columns信息，发现表里出现了被kill的索引信息（见下图），且索引状态为vaild。

为了验证索引的有效性，我们使用索引列进行搜索，并用 hint 强制，结果都是走全表扫描（实验见下图）。也就是说，这个索引是有问题的。

既然索引不能被使用，那就要对这个索引进行清理。

直接drop是不可行的，并且该数据库版本为11.2.0.4，drop index并不支持online操作，故当时尝试了下是否可以在线重建索引： 

可以看到，报了ora-08104错误。

于是寻找一些资料得知：create index online时会修改数据库字典表obj$，并在该索引用户下创建表sys_journal_obj#（具体对象号）和标记表ind$、ind_online$。如果服务器进程在语句执行过程中意外终止的话，可能会导致相关在ind$标记位信息及在线日志中间表不能及时处理及清除。这将导致对该索引后续操作因ora-08104错误而无法继续。

对此进行验证，首先查出该索引对应的object_id：

然后使用dbms_repair进行清理：

对于此函数，oracle 11g R2官方文档介绍如下：

This function performs a manual cleanup of failed or interrupted online index builds or rebuilds. This action is also performed periodically by SMON, regardless of user-initiated cleanup.

This function returns TRUE if all indexes specified were cleaned up and FALSE if one or more indexes could not be cleaned up.

用该函数尝试清理不成功，查看数据库的 alert 日志发现出现如下一条信息：

这个方法此时是行不通的。

查阅资料还有一个方法就是将此索引对应表的TM锁进程提交，再进行对索引清理。于是在晚上检修时，将insert进程commit后，对该索引再进行删除操作便成功了，然后重新创建索引。

重建后验证执行计划，看到是走索引扫描则证明索引可用（见下图）。

这起事件主要是因为服务器进程在执行create index online时意外终止，导致生成的索引并不可用，而由于数据表上的insert进程一直未提交，导致索引进程的等待。

我们重新来看下这个案例，导致情况发生的主要原因是在创建索引时被中断，那我们在创建索引时需要注意哪些来避免这类情况发生呢？

创建索引的方式主要是create index和create index online，create index动作会阻塞其他会话修改索引字段，直到索引创建结束。当表对象上存在DML语句尚未提交时，若此时进行create index时则会报ora-00054:resource busy and acquire with nowait specified错误。而create index online则允许其他会话修改索引字段，但如果修改索引字段的会话没有commit或rollback，索引创建进程将会被阻塞，直到holder进程完成commit或rollback后，索引才能创建完成。

若create index online进程正常执行完成则索引无误，但若出现以下操作时，均有可能出现索引异常的情况：

对于这类问题，解决的主要方法有：

针对上图几种方法，首先：

方法一使用存储过程dbms_repair.online_index_clean进行清理，这种方式不仅可以清理所有处于online built的索引，还可以指定具体的object_id。

清理所有在线索引的语法：

declare isClean boolean; begin isClean := FALSE; while isClean=FALSE loop isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id, dbms_repair.lock_wait); dbms_lock.sleep(2); end loop; exception when others then RAISE; end; /

指定具体object_id：

declare a boolean; begin a := dbms_repair.online_index_clean(1760,dbms_repair.lock_wait); end; /

但该方法具有版本限制，根据官方mos资料，在10.2及以上版本中均可以使用该存储过程，而针对10.2之前的版本，9.2.0.7和10.1.0.4可通过打上bug3805539的patch后方可使用dbms_repair包。（具体参见mos文档：Bug 3805539 - Add DBMS_REPAIR.ONLINE_INDEX_CLEAN to manually clean up failed ONLINE builds (Doc ID 3805539.8)）。

方法二使用ORADEBUG唤醒SMON进程进行清理

主要步骤为：

首先使用sys用户登录数据库，根据以下sql查出smon的pid： select pid,spid from v$process p,v$bgprocess b where b.paddr=p.addr and name='SMON'; 然后进行： ORADEBUG WAKEUP pid                         -- pid为上条sql查出的pid值

 

方法三重启数据库实例由SMON进程清理，需要安排停机时间，停应用然后重启数据库，此时相关表上没有了DML操作，SMON进程会完成自动清理。

注：方法四因需要手工修改数据库的内部数据字典，风险较大，不推荐在生产系统中使用，容易造成其他不可预见的问题。

原创：李晴晴。

投稿：有投稿意向技术人请在微信对话框留言。

转载：意向文章下方留言。

更多精彩请关注 “数据和云” 微信 。