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/<库名>/<实例名>/trace
grep -i -w 'unusable' -B2 -A2 alert_<实例名>.log

2.如果是全局分区索引，建议将索引删掉后重建。对于本地分区索引，重建不可用索引分区或索引子分区。

参考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;