Oracle 9i 監控索引使用率，精準刪除無用索引提升 DML 效能

為什麼要清理無用索引？

每個索引在 INSERT/UPDATE/DELETE 時都需同步維護。過度索引會嚴重拖慢關鍵表的寫入效能。9i 前無法知曉索引是否被查詢使用，9i 引入 ALTER INDEX ... MONITORING USAGE 機制，配合 V$OBJECT_USAGE 視圖即可識別長期未被使用的索引。

一鍵開啟所有非系統索引監控

SPOOL run_monitor.sql
SELECT 'ALTER INDEX '||owner||'.'||index_name||' MONITORING USAGE;'
FROM dba_indexes
WHERE owner NOT IN ('SYS','SYSTEM');
SPOOL OFF
@run_monitor

觀察期結束後查詢結果

SELECT index_name, table_name, monitoring, used
FROM v$object_usage
WHERE used = 'NO';  -- 從未被使用

USED 欄位為 YES/NO，不計算使用次數，但足以找出候選刪除對象。建議觀察期覆蓋完整業務週期（含月結、年結批次）。

實務建議

• 先 ALTER INDEX ... NOMONITORING USAGE 關閉監控再刪除，避免殘留監控狀態。
• 刪除前先匯出 DDL（DBMS_METADATA.GET_DDL）備份。
• 功能性索引（Function-Based Index）同樣適用此法。
• 若索引用於外鍵約束檢查，雖未出現在查詢計畫，實際上被 DML 內部使用，不可輕易刪除。