Oracle 索引監控與分析自動化：每日統計刪除比率預防效能退化

背景痛點

索引長期運行會產生大量「邏輯刪除」項目（標記為刪除但未物理移除的 Leaf Row），導致：

• 索引體積膨脹，掃描更多區塊。
• 高度增加，單行存取變慢。
• 緩衝區命中率下降。

需建立每日自動化監控，量化「刪除比率」決定是否 REBUILD 或 COALESCE。

核心物件設計

1. 監控統計表

CREATE TABLE t_analyz_monitor_index (
    f_index_name   VARCHAR2(50),
    f_del_lf_rows  NUMBER,
    f_lf_rows      NUMBER,
    f_rate         NUMBER(4,2),          -- 刪除比率 (%)
    f_monitor_date DATE DEFAULT SYSDATE NOT NULL
);

2. 歷史明細表（保留每次 ANALYZE 完整輸出）

CREATE TABLE t_analyz_index_stats AS 
SELECT * FROM index_stats WHERE 1=0;  -- 結構同 INDEX_STATS 視圖

自動化分析預存程序

CREATE OR REPLACE PROCEDURE p_analyz_day_index_stats AS
    v_sql VARCHAR2(200);
BEGIN
    -- 僅處理當前 Schema 下非 LOB 索引
    FOR idx_rec IN (
        SELECT index_name 
        FROM user_indexes 
        WHERE index_type <> 'LOB'
    ) LOOP
        -- 1. 驗證結構並填入 INDEX_STATS
        v_sql := 'ANALYZE INDEX ' || idx_rec.index_name || ' VALIDATE STRUCTURE';
        EXECUTE IMMEDIATE v_sql;
        
        -- 2. 歸檔完整統計快照
        INSERT INTO t_analyz_index_stats
        SELECT * FROM index_stats;
        
        -- 3. 計算並記錄刪除比率
        INSERT INTO t_analyz_monitor_index (
            f_index_name, f_del_lf_rows, f_lf_rows, f_rate
        )
        SELECT 
            name, 
            del_lf_rows, 
            lf_rows, 
            ROUND(del_lf_rows * 100 / DECODE(lf_rows + del_lf_rows, 0, 1), 2)
        FROM index_stats;
    END LOOP;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        -- 建議寫入錯誤日誌表
        RAISE;
END;
/
-- 排程：DBMS_SCHEDULER 每日低峰期執行

關鍵指標解讀與行動閾值

進階擴充：自動化整理決策

-- 產生整理腳本（供 DBA 審核後執行）
SELECT 
    'ALTER INDEX ' || f_index_name || 
    CASE 
        WHEN f_rate > 30 THEN ' REBUILD ONLINE;'
        WHEN f_rate > 20 THEN ' COALESCE;'
        ELSE ' -- OK;'
    END AS ddl_script
FROM t_analyz_monitor_index
WHERE f_monitor_date = TRUNC(SYSDATE)
  AND f_rate > 20
ORDER BY f_rate DESC;

注意事項與避坑

1. ANALYZE VALIDATE STRUCTURE 會鎖定索引（Share 模式），大索引執行時間較長，務必安排在業務低峰期。
2. 11g+ 推薦改用 DBMS_STATS.GATHER_INDEX_STATS，無鎖、支援採樣、可並行，但無法直接取得 DEL_LF_ROWS。若需刪除比率，仍需 ANALYZE 或查詢 INDEX_STATS（需先 ANALYZE）。
3. LOB 索引排除：INDEX_TYPE = 'LOB' 為系統維護，不需人工干預。
4. 分區索引：USER_IND_PARTITIONS 需逐分區分析，邏輯需擴充。
5. 空間回收：COALESCE 僅合併相鄰 Leaf Block，不釋放空間；REBUILD 重建結構並可縮小段，但需雙倍臨時空間。

監控儀表板查詢範例

-- 今日異常索引
SELECT * FROM t_analyz_monitor_index
WHERE f_monitor_date = TRUNC(SYSDATE)
  AND f_rate > 20
ORDER BY f_rate DESC;

-- 索引健康度趨勢 (最近 30 天)
SELECT 
    f_index_name,
    f_monitor_date,
    f_rate,
    LAG(f_rate) OVER (PARTITION BY f_index_name ORDER BY f_monitor_date) AS prev_rate
FROM t_analyz_monitor_index
WHERE f_monitor_date >= TRUNC(SYSDATE) - 30
ORDER BY f_index_name, f_monitor_date;

總結

以 ANALYZE INDEX VALIDATE STRUCTURE 為核心，建立「每日分析 → 統計歸檔 → 比率計算 → 閾值告警 → 整理決策」閉環，能將索引維護從被動救火轉為主動治理。關鍵在於：低峰期執行、閾值分級、自動產生整理腳本供審核、趨勢分析預測惡化。