在實踐中ORACLE數(shù)據(jù)庫存在不可用索引會引發(fā)性能問題。

所謂的不可用索引，是指索引自身出了問題，不能被所有SQL使用到。這與因SQL寫法不當(dāng)而無法使用索引的索引失效情況不同。

當(dāng)索引變?yōu)椴豢捎脮r，原本可以使用該索引的SQL都將無法使用該索引，只能選擇全表掃描或全分區(qū)掃描，這將導(dǎo)致SQL執(zhí)行效率大幅下降。如果并發(fā)高一些，將會耗盡數(shù)據(jù)庫主機(jī)硬件資源，導(dǎo)致所有請求響應(yīng)超時。

導(dǎo)致索引不可用的情況通常有：

1.當(dāng)用truncate/drop/exchange 操作分區(qū)時，全局索引會失效。增加 update global indexes 參數(shù)，全局索引就不會失效了。

2.exchange的臨時表沒有索引，或者有索引，沒有用including indexes的關(guān)鍵字，會導(dǎo)致局部的索引失效，就是某個分區(qū)失效。

重建局部索引只能用alter index local_idx rebuild partition p1這樣的方式

3.分區(qū)表SPLIT的時候，如果MAX區(qū)中已經(jīng)有記錄了，這個時候SPLIT就會導(dǎo)致有記錄的新增分區(qū)的局部索引失效!

4.對表執(zhí)行move操作，將導(dǎo)致表上所有索引變?yōu)椴豢捎谩?/p>

5.sqlldr 如帶有 skip_index_maintenance=true 參數(shù)，數(shù)據(jù)導(dǎo)入時將不維護(hù)索引，會導(dǎo)致表上所有索引不可用。所以，在完成數(shù)據(jù)導(dǎo)入后，需重建表上所有索引。

通過檢查不可用索引，排查索引不可用的原因，修正存在缺陷的維護(hù)操作，避免生產(chǎn)事件的發(fā)生,只重建不可用索引，是遠(yuǎn)遠(yuǎn)不夠的。尤其小心不同日期同一時刻導(dǎo)致索引不可用的情況。

一旦出現(xiàn)不可用索引，就特別容易引發(fā)生產(chǎn)事故。所以，請?zhí)貏e重視，排查原因，消除隱患。

修復(fù)建議：

1.找出導(dǎo)致索引不可用的原因

可從Oracle alert日志中查找索引不可用的日志，根據(jù)時間點排查相關(guān)表的維護(hù)類操作，進(jìn)而確定引發(fā)的原因。

ssh登陸oracle服務(wù)器上，查看日志命令：

cd /home/db/oracle/diag/rdbms/<庫名>/<實例名>/trace
grep -i -w 'unusable' -B2 -A2 alert_<實例名>.log

2.如果是全局分區(qū)索引，建議將索引刪掉后重建。對于本地分區(qū)索引，重建不可用索引分區(qū)或索引子分區(qū)。

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