Oracle 索引監控實戰：精準識別未用索引降低 DML 開銷

為什麼要監控索引？

大量無效索引會：

• 增加 INSERT/UPDATE/DELETE 維護開銷
• 佔用空間、緩存、備份時間
• 誤導優化器產生劣質計劃

Oracle 內建 MONITORING USAGE 機制，零成本識別「是否被使用」。

操作流程

1. 查看目標表索引

SELECT INDEX_NAME, TABLE_NAME, NUM_ROWS
FROM DBA_INDEXES
WHERE TABLE_NAME = 'WEBSITE_VIEW_TB';

2. 開啟監控

ALTER INDEX CURRTIME_IDX MONITORING USAGE;

3. 監控期間運行業務查詢

SELECT COUNT(*) FROM LOG.WEBSITE_VIEW_TB
WHERE CURRTIME BETWEEN TO_DATE('2013-01-27','YYYY-MM-DD') AND TO_DATE('2013-01-28','YYYY-MM-DD');

4. 查看監控結果

SELECT * FROM V$OBJECT_USAGE;
-- 關鍵欄位：
-- USED = YES/NO  監控期間是否被使用過
-- MONITORING = YES/NO  是否正在監控
-- START_MONITORING / END_MONITORING  監控時間窗

5. 關閉監控（釋放資源）

ALTER INDEX CURRTIME_IDX NOMONITORING USAGE;

結果解讀範例

局限性與補充策略

• 僅記錄「是否用過」，不統計次數/頻率/最近使用時間
• 長期監控會輕微增加解析開銷，建議**分批次、分時段**監控
• 結合 DBA_HIST_SQL_PLAN、V$SQL_PLAN 分析具體 SQL 使用情況
• 12c+ 可用 DBA_INDEX_USAGE（需 DBMS_STATS.SET_GLOBAL_PREFS('INDEX_USAGE_TRACKING','TRUE')）獲得更豐富統計

維護建議

1. 定期（如月度）對核心大表索引輪巡監控
2. USED=NO 且無業務特殊需求（如外鍵支撐、定期批次報表）的索引 → 評估刪除
3. 刪除前務必在測試環境驗證無負面影響
4. 保留監控日誌供審計追溯