Oracle数据库存在不可用索引性能问题 _Oracle

在实践中ORACLE数据库存在不可用索引会引发性能问题。

文章插图
【Oracle数据库存在不可用索引性能问题】所谓的不可用索引，是指索引自身出了问题，不能被所有SQL使用到。这与因SQL写法不当而无法使用索引的索引失效情况不同。
当索引变为不可用时，原本可以使用该索引的SQL都将无法使用该索引，只能选择全表扫描或全分区扫描，这将导致SQL执行效率大幅下降。如果并发高一些，将会耗尽数据库主机硬件资源，导致所有请求响应超时。
导致索引不可用的情况通常有：1.当用truncate/drop/exchange 操作分区时，全局索引会失效。增加 update global indexes 参数，全局索引就不会失效了。
2.exchange的临时表没有索引，或者有索引，没有用including indexes的关键字，会导致局部的索引失效，就是某个分区失效。
重建局部索引只能用alter index local_idx rebuild partition p1这样的方式
3.分区表SPLIT的时候，如果MAX区中已经有记录了，这个时候SPLIT就会导致有记录的新增分区的局部索引失效!
4.对表执行move操作，将导致表上所有索引变为不可用。
5.sqlldr 如带有 skip_index_mAIntenance=true 参数，数据导入时将不维护索引，会导致表上所有索引不可用。所以，在完成数据导入后，需重建表上所有索引。
通过检查不可用索引，排查索引不可用的原因，修正存在缺陷的维护操作，避免生产事件的发生,只重建不可用索引，是远远不够的。尤其小心不同日期同一时刻导致索引不可用的情况。
一旦出现不可用索引，就特别容易引发生产事故。所以，请特别重视，排查原因，消除隐患。
修复建议：1.找出导致索引不可用的原因
可从Oracle alert日志中查找索引不可用的日志，根据时间点排查相关表的维护类操作，进而确定引发的原因。
ssh登陆oracle服务器上，查看日志命令：
cd /home/db/oracle/diag/rdbms/<库名>/<实例名>/tracegrep -i -w 'unusable' -B2 -A2 alert_<实例名>.log2.如果是全局分区索引，建议将索引删掉后重建。对于本地分区索引，重建不可用索引分区或索引子分区。
参考SQL语句：

select * from user_indexes where status = 'UNUSABLE';alter index <index_name> rebuild online;select * from user_ind_partitions where status = 'UNUSABLE'; alter index <index_name> rebuild partition <partition_name> online;select * from user_ind_subpartitions where status = 'UNUSABLE'; alter index <index_name> rebuild subpartition <subpartition_name> online;