Oracle 9i 索引監控功能：識別未使用索引釋放空間

背景

索引加速查詢但佔用空間 (常超過表大小)、增加 DML 開銷。9i 前難知索引是否被用，導致大量廢棄索引長期佔用資源。

啟用/停用監控

ALTER INDEX idx_name MONITORING USAGE;
ALTER INDEX idx_name NOMONITORING USAGE;

監控結果視圖 V$OBJECT_USAGE

SELECT index_name, table_name, monitoring, used,
       start_monitoring, end_monitoring
FROM v$object_usage;

全庫視圖 V$ALL_OBJECT_USAGE (自建)

CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE (
  OWNER, INDEX_NAME, TABLE_NAME, MONITORING, USED,
  START_MONITORING, END_MONITORING
) AS
SELECT u.name, io.name, t.name,
       DECODE(BITAND(i.flags,65536),0,'NO','YES'),
       DECODE(BITAND(ou.flags,1),0,'NO','YES'),
       ou.start_monitoring, ou.end_monitoring
FROM sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
WHERE i.obj# = ou.obj#
  AND io.obj# = ou.obj#
  AND t.obj# = i.bo#
  AND io.owner# = u.user#;

GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO PUBLIC;
CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE;

運作細節

• 每次 MONITORING USAGE 重置統計，清除舊記錄、記錄新開始時間。
• NOMONITORING USAGE 記錄結束時間、停止後續監控。
• 刪除被監控索引 → 視圖記錄同步刪除。
• 僅顯示當前用戶 Schema 索引，全庫需用自建視圖。

實務建議

1. 對所有非 PK/FK 索引啟用監控，觀察一個完整業務週期 (含月結、季結報表)。
2. USED = 'NO' 且無業務特殊用途 (如外鍵鎖避免全表掃描) → 評估 DROP INDEX。
3. 定期 (如每季) 重置監控，捕捉應用變更帶來的索引使用變化。
4. 配合 V$SEGMENT_STATISTICS 看 Logical Reads/Physical Reads 量化索引貢獻。