Oracle 索引監控與隱形索引：精準識別廢棄索引、零風險驗證新索引效能

痛點

生產庫常積累百個索引，多數從未使用或拖慢 DML。傳統做法直接 DROP 風險大：可能遺漏周期性作業、Hint 強制使用、外鍵鎖優化等隱性依賴。

方案一：索引使用監控

ALTER INDEX pk_emp MONITORING USAGE;
ALTER INDEX ix_emp_sal MONITORING USAGE;

-- 經過一個業務週期後查詢
SELECT index_name, table_name, monitoring, used,
       start_monitoring, end_monitoring
FROM v$object_usage;

• USED = YES：曾被執行計劃採用。
• USED = NO：監控期間未被使用，候選刪除對象。

方案二：隱形索引——零重建風險驗證

建立/轉換隱形索引

CREATE INDEX ix_emp_sal ON emp(sal) INVISIBLE;
-- 或
ALTER INDEX ix_emp_sal INVISIBLE;

特性

• 優化器忽略隱形索引（CBO/RBO 均不考慮）。
• DML 仍正常維護索引結構，無額外開銷。
• 可透過參數或 Hint 強制啟用驗證：

-- Session 級啟用
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- Hint 強制使用（需參數為 TRUE 才生效）
SELECT /*+ INDEX(emp ix_emp_sal) */ ename FROM emp WHERE sal = 1500;

還原可見

ALTER INDEX ix_emp_sal VISIBLE;  -- 瞬間生效，無重建開銷

查詢隱形索引清單

SELECT index_name, visibility FROM dba_indexes WHERE visibility = 'INVISIBLE';

最佳實踐流程

1. 對疑似廢棄索引啟用 MONITORING USAGE 覆蓋完整業務週期。
2. USED = NO 者先設 INVISIBLE 觀察 1~2 週，無異常再 DROP。
3. 新索引上線前先 CREATE ... INVISIBLE，配合 optimizer_use_invisible_indexes 在測試/預發環境驗證執行計劃與效能。
4. 定期清理監控記錄：ALTER INDEX ... NOMONITORING USAGE;。