您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 索引監控全攻略：從 8i 到 19c 的演進與實戰腳本

編輯：Oracle 資料庫基礎

為什麼要監控索引使用情況？

研究顯示：資料庫中超過 75% 的索引從未被使用，或未按預期方式被使用。這些「僵屍索引」不僅佔用儲存空間，更在每次 DML 操作時產生維護開銷（寫入 Redo/Undo、分裂合併、統計資訊更新）。定期識別並移除未用索引，是資料庫效能調優的高報酬動作。

Oracle 8i/9i：基於共享池計畫的被動分析

8i 時代：EXPLAIN PLAN 掃描 V$SQLAREA

原理：對共享池中所有 SQL 執行 EXPLAIN PLAN，解析 PLAN_TABLE 中 OPERATION='INDEX' 的記錄。提供完整腳本（見原文），但存在嚴重限制：

假設 OPTIMIZER_GOAL 為常數，忽略實際 V$SQLAREA.OPTIMIZER_MODE
需掃描所有共享 SQL，對繁忙系統造成明顯負擔
依賴 V$SQLAREA.VERSION_COUNT=1，不支援多子游標
無法捕捉已老化淘汰的 SQL

建議：僅適用於開發/測試環境或低峰期離線分析。

9i 突破：V$SQL_PLAN 即時計畫視圖

9i 引入 V$SQL_PLAN，儲存實際執行計畫（非估算），查詢大幅簡化：

SELECT OBJECT_OWNER, OBJECT_NAME, OPTIONS, COUNT(*)
FROM V$SQL_PLAN
WHERE OPERATION = 'INDEX' AND OBJECT_OWNER != 'SYS'
GROUP BY OBJECT_OWNER, OBJECT_NAME, OPERATION, OPTIONS
ORDER BY COUNT(*) DESC;

但本質仍受限於共享池保留時間，長時間未執行的 SQL 計畫會被淘汰，導致統計不完整。

9i 里程碑：ALTER INDEX MONITORING USAGE

9i 引入原生監控機制，零效能影響、精準記錄「是否被使用」：

啟用監控

ALTER INDEX schema.index_name MONITORING USAGE;

查詢監控結果

SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, 
       START_MONITORING, END_MONITORING
FROM V$OBJECT_USAGE;

MONITORING='YES'：正在監控中
USED='YES'：監控期間至少被使用一次
START/END_MONITORING：時間戳記

批次啟用所有使用者索引（排除 SYS/SYSTEM）

BEGIN
  FOR rec IN (
    SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' MONITORING USAGE' AS cmd
    FROM DBA_INDEXES
    WHERE OWNER NOT IN ('SYS','SYSTEM') AND INDEX_TYPE = 'NORMAL'
  ) LOOP
    EXECUTE IMMEDIATE rec.cmd;
  END LOOP;
END;
/
-- 一段時間後（如一週/一月）查詢 V$OBJECT_USAGE，USED='NO' 即為候選移除索引
-- 停用監控
ALTER INDEX schema.index_name NOMONITORING USAGE;

實戰驗證：外鍵索引在級聯刪除時的使用

測試 HR 結構：刪除 DEPARTMENTS 父表記錄觸發 EMPLOYEES 子表級聯刪除，V$OBJECT_USAGE 顯示 EMP_DEPT_FK 索引 USED='YES'，證明外鍵索引在 DML 時確實被存取（避免全表掃描鎖定父表）。

跨使用者查詢視圖：V$ALL_OBJECT_USAGE

CREATE OR REPLACE VIEW V$ALL_OBJECT_USAGE AS
SELECT IO.NAME INDEX_NAME, T.NAME TABLE_NAME,
       DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,
       DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
       OU.START_MONITORING, OU.END_MONITORING
FROM SYS.OBJ$ IO, SYS.OBJ$ T, SYS.IND$ I, SYS.OBJECT_USAGE OU
WHERE I.OBJ# = OU.OBJ# AND IO.OBJ# = OU.OBJ# AND T.OBJ# = I.BO#;
GRANT SELECT ON V$ALL_OBJECT_USAGE TO PUBLIC;
CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR V$ALL_OBJECT_USAGE;

補充技巧：近期被存取的索引（X$BH 緩衝區分析）

SELECT DISTINCT B.OWNER, B.SEGMENT_NAME
FROM X$BH A, DBA_EXTENTS B
WHERE B.FILE_ID = A.DBAFIL
  AND A.DBABLK BETWEEN B.BLOCK_ID AND B.BLOCK_ID + B.BLOCKS - 1
  AND B.SEGMENT_TYPE = 'INDEX'
  AND B.OWNER NOT IN ('SYS','SYSTEM');

此法掃描緩衝區標頭，反映近期實際被讀入記憶體的索引區段，耗時較長，建議離峰執行。

現代版本（12c/19c）增強功能

12c：DBA_INDEX_USAGE 視圖自動記錄索引使用統計（需啟用 DBMS_STATS.SET_GLOBAL_PREFS('INDEX_USAGE_TRACKING', 'TRUE')），無需手動 MONITORING。
19c：USER_INDEX_USAGE 提供更豐富統計（掃描次數、最後使用時間、是否用於查詢/DML/DDL），支援自動化清理策略。