清除 Oracle 無用索引提升 DML 效能：監控用法、V$OBJECT_USAGE 實戰

痛點

過度索引導致 INSERT/UPDATE/DELETE 每行需維護大量索引結構，**DML 效能嚴重下降**。許多索引從未被查詢使用。

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

步驟 1：開啟所有非系統索引監控

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

步驟 2：等待代表性負載週期（至少一個完整業務週期）

讓日常批次、報表、線上交易自然跑完。

步驟 3：查詢監控結果

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

• USED = 'NO' 表示監控期間**從未被優化器採用**。
• 不提供使用次數，僅是/否，但足以識別「從未用到」的索引。

步驟 4：審慎刪除

1. 確認非 PK/UK/FK 支撐索引（刪除會失效約束）。
2. 確認非外鍵欄位索引（刪除會導致 FK DML 鎖升級為表鎖）。
3. 確認非統計資訊收集依賴的索引（DBMS_STATS 可能採樣索引葉塊）。
4. 在維護窗口執行 DROP INDEX，並收集受影響表統計。

進階：細粒度監控

-- 單一索引開啟/關閉
ALTER INDEX scott.emp_idx MONITORING USAGE;
ALTER INDEX scott.emp_idx NOMONITORING USAGE;

-- 查詢特定索引
SELECT * FROM v$object_usage WHERE index_name = 'EMP_IDX';

自動化建議

• 建立排程 Job：每季度啟動監控 → 兩週後產生報表 → DBA 審核 → 刪除確認無用索引。
• 新建索引上線前，先在測試環境跑負載驗證 USED=YES，再推生產。
• 結合 DBA_HIST_SQL_PLAN (AWR) 分析歷史執行計畫，雙重確認。