清除無用索引提升 DML 效能：Oracle 9i 監控功能實戰

痛點

過度索引是 DML 變慢主因：每筆 INSERT/UPDATE/DELETE 皆需同步維護所有索引。許多 DBA 見 WHERE 條件有欄位就建索引，導致關鍵表索引過剩，嚴重拖垮寫入效能。

Oracle 9i 解決方案：索引使用監控

9i 引入 ALTER INDEX ... MONITORING USAGE，配合 V$OBJECT_USAGE 即可識別長期未被使用的索引。

三步走腳本

1. 開啟全庫索引監控：

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

2. 等待業務跑完完整週期（至少覆蓋所有批次作業、報表查詢）。
3. 查詢未使用索引：

   SELECT index_name, table_name, monitoring, used
   FROM   v$object_usage
   WHERE  used = 'NO';

   USED=NO 即為候選刪除對象（不提供使用次數，但足夠定性）。

後續動作

• 關閉監控：ALTER INDEX ... NOMONITORING USAGE;（釋放少量共用池記憶體）。
• 刪除前驗證：確認非外鍵索引、非唯一性約束支撐、非統計資訊收集依賴。
• 分批刪除，觀察 DML 效能提升。

進階建議

• 11g+ 可用 DBA_INDEX_USAGE（需診斷包授權）獲取更豐富統計。
• 結合 V$SEGMENT_STATISTICS 看索引讀寫比，精準決策。
• 建立定期巡檢機制，納入變更管理流程。