Oracle 9i 索引監控功能：識別未使用索引完整指南

索引的雙面性

索引加速查詢，但佔用空間（常超過表本身），且增加 DML 維護開銷。9i 前難以知曉索引是否被使用，導致大量無效索引浪費資源。

啟用/停用索引監控

ALTER INDEX index_name MONITORING USAGE;   -- 開始
ALTER INDEX index_name NOMONITORING USAGE;  -- 停止

監控結果視圖

V$OBJECT_USAGE（僅目前 Schema）與自訂 V$ALL_OBJECT_USAGE（全資料庫，含 OWNER 欄位）。

-- V$OBJECT_USAGE 定義
CREATE OR REPLACE VIEW SYS.V$OBJECT_USAGE (
  INDEX_NAME, TABLE_NAME, MONITORING, USED,
  START_MONITORING, END_MONITORING
) AS
SELECT 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
WHERE io.owner# = USERENV('SCHEMAID')
  AND i.obj# = ou.obj#
  AND io.obj# = ou.obj#
  AND t.obj# = i.bo#;

欄位說明

• INDEX_NAME / TABLE_NAME：物件名稱
• MONITORING：YES/NO 是否正在監控
• USED：YES/NO 監控期間是否曾被使用
• START_MONITORING / END_MONITORING：監控時間區間

每次啟用監控會重置統計；停用則記錄結束時間。刪除被監控索引會自動清除視圖記錄。

自動化腳本：全資料庫索引監控與報表

1. 啟動所有索引監控

#!/bin/ksh
# start_index_monitoring.sh  
sqlplus -s system/$1@$2 <

2. 停止所有索引監控
# stop_index_monitoring.sh  
# 同理，將 MONITORING 改為 NOMONITORING

3. 產出未使用索引報表
# identify_unused_index.sh  
sqlplus -s system/$1@$2 <

報表範例輸出
Unused Indexes Report
OWNER  INDEX_NAME            TABLE_NAME     USE
HR     DEPT_ID_PK            DEPARTMENTS    NO
HR     DEPT_LOCATION_IX      DEPARTMENTS    NO
HR     EMP_DEPARTMENT_IX     EMPLOYEES      NO
... (更多) ...
依此報表可安全移除無效索引，釋放空間並降低 DML 開銷。