全表扫描、建议创建索引 在日常的数据库优化工作中,发现某sql执行效率较低,并且运行频率较高。查看其执行计划发现走全表扫描,而sql的谓词条件是对某一number类型列进行精确匹配,而该列的选择性较好,故建议厂商运维人员在该列上创建索引,来优化sql的效率。 在线创建索引、迟迟不返回便kill 相关运维人员在和开发人员确认可行性后,通过PLSQL Developer客户端在生产环境执行创建索引操作,报ora-00054错误,见下图(部分敏感信息已打码) 然后在创建索引语句后加上了online关键词,继续在PLSQL Developer客户端执行,但一直没有返回结果,故联系我看下原因。 一般,在出现ora-00054报错时,主要原因就可能是该表存在尚未commit或rollback的DML命令,而在线创建索引没有返回的原因也应该是被别的会话进程阻塞了,有这个思路就去验证,查看当前系统的锁便发现: 查看 holder 的 sql_id 文本: 而 waiter 的确实是创建索引的进程: 确实存在进程对该表进行 insert 操作,而这个进程一直未提交,咨询相关人员表示这个进程存在缓存数据,由于业务关系不能将这个进程 commit 或者 kill。于是运维人员就把创建索引这个进程 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官方文档介绍如下:
用该函数尝试清理不成功,查看数据库的 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进程正常执行完成则索引无误,但若出现以下操作时,均有可能出现索引异常的情况: 对于这类问题,解决的主要方法有: 针对上图几种方法,首先:
原创:李晴晴。 投稿:有投稿意向技术人请在公众号对话框留言。 |
|