Oracle 索引重建自動化實戰：從碎片偵測到排程維護

大量修改/刪除操作會導致索引結構碎片化、空間浪費與效能下降。本文提供完整自動化方案：動態產生重建腳本、碎片度分析判斷、排程執行與異常處理，打造零人力介入的索引維護機制。

核心邏輯架構

1. 掃描目標：依使用者/大小門檻篩選索引。
2. 產生腳本：保留原儲存參數，產出 ALTER INDEX ... REBUILD 指令。
3. 碎片評估：ANALYZE INDEX ... VALIDATE STRUCTURE 計算刪除葉列比率。
4. 條件執行：僅重建碎片度超標 (如 > 20%) 索引。
5. 排程容錯：忽略 ORA-00054 (資源忙) 錯誤，下一輪重試。

步驟 1：動態產生重建腳本 (SQL*Plus 版)

SET ECHO OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 TERMOUT ON HEADING OFF
ACCEPT username CHAR PROMPT '輸入索引擁有者: '
SPOOL rebuild_&username..sql

SELECT 'REM +-----------------------------------------------+' || CHR(10) ||
       'REM | INDEX NAME : ' || owner || '.' || segment_name ||
       LPAD('|', 33 - (LENGTH(owner) + LENGTH(segment_name))) || CHR(10) ||
       'REM | BYTES      : ' || bytes ||
       LPAD('|', 34 - LENGTH(bytes)) || CHR(10) ||
       'REM | EXTENTS    : ' || extents ||
       LPAD('|', 34 - LENGTH(extents)) || CHR(10) ||
       'REM +-----------------------------------------------+' || CHR(10) ||
       'ALTER INDEX ' || owner || '.' || segment_name || CHR(10) ||
       'REBUILD' || CHR(10) ||
       'TABLESPACE ' || tablespace_name || CHR(10) ||
       'STORAGE (' || CHR(10) ||
       '  INITIAL '     || initial_extent  || CHR(10) ||
       '  NEXT '        || next_extent     || CHR(10) ||
       '  MINEXTENTS '  || min_extents     || CHR(10) ||
       '  MAXEXTENTS '  || max_extents     || CHR(10) ||
       '  PCTINCREASE ' || pct_increase    || CHR(10) ||
       ');' || CHR(10) || CHR(10)
FROM dba_segments
WHERE segment_type = 'INDEX'
  AND owner = UPPER('&username')
ORDER BY owner, bytes DESC;

SPOOL OFF

Windows 路徑調整：SPOOL C:\Oracle\rebuild_&username..sql

加入大小門檻：AND bytes > &max_bytes

步驟 2：碎片度精準評估

ANALYZE INDEX &index_name VALIDATE STRUCTURE;

COL name FORMAT A30
COL del_lf_rows HEADING 'Deleted|Leaf Rows' FORMAT 99999999
COL lf_rows_used HEADING 'Used|Leaf Rows' FORMAT 99999999
COL ratio HEADING '% Deleted|Leaf Rows' FORMAT 999.99999

SELECT name,
       del_lf_rows,
       lf_rows - del_lf_rows AS lf_rows_used,
       TO_CHAR(del_lf_rows / lf_rows * 100, '999.99999') AS ratio
FROM index_stats
WHERE name = UPPER('&index_name');

判斷標準：ratio > 15~20% 即建議重建。

步驟 3：人工覆核與參數微調

• 檢視產出腳本，確認 PCTINCREASE 非 0 (建議改 0 避免碎片)。
• 移除極繁忙表索引 (避免 ORA-00054 頻發)，改用 REBUILD ONLINE 或 DBMS_REDEFINITION。
• 調整 TABLESPACE 子句，配合儲存分層策略移動索引。

步驟 4：納入排程自動化 (DBMS_SCHEDULER 範例)

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'IDX_REBUILD_MONTHLY',
    job_type        => 'EXECUTABLE',
    job_action      => '/bin/sh -c "sqlplus -s / as sysdba @/scripts/rebuild_idx.sql"',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MONTHLY; BYDAY=1; BYHOUR=2; BYMINUTE=0', -- 每月 1 號 02:00
    enabled         => TRUE,
    comments        => '月度索引重建，自動跳過鎖衝突'
  );
END;
/

常見坑點與對策

進階擴充：整合 Enterprise Manager / Cloud Control

可將碎片評估邏輯封裝為 DBMS_SQL 動態執行，並寫入自訂 Metric，透過 EM 告警觸發重建作業，實現「感知-決策-執行」閉環。