Oracle 數據庫性能持久保護四大支柱：分區、索引重建、碎片整理、自由空間管理

前言

Oracle 數據庫長期穩定高效運行，依賴於對**數據組織結構**的持續治理。本文系統闡述四大核心維護支柱：分區表策略、索引重建機制、段碎片整理、自由區間合併，給出生產級實施腳本、調度策略、監控指標與避坑指南，構建「自我修復」的存儲層健康體系。

支柱一：分區表 —— 大表治理的核心武器

1. 分區類型選擇指南

2. 分區表創建範例 (INTERVAL-RANGE-HASH 複合)

CREATE TABLE sales_orders (
    order_id       NUMBER GENERATED AS IDENTITY,
    cust_id        NUMBER NOT NULL,
    order_date     DATE NOT NULL,
    region_code    VARCHAR2(10) NOT NULL,
    amount         NUMBER(12,2),
    status         VARCHAR2(10),
    ...
)
PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 8
( PARTITION p_before_2020 VALUES LESS THAN (DATE '2020-01-01') )
ENABLE ROW MOVEMENT  -- 支援跨分區更新行移動
PARALLEL 8
COMPRESS FOR OLTP;  -- 11g+ OLTP 壓縮

3. 分區索引：局部 (LOCAL) 為主，全局 (GLOBAL) 為輔

• Local Index (局部)：索引分區與表分區一一對應，**首選**。維護無影響 (分區 TRUNCATE/DROP 自動同步索引分區)、支援分區剪枝、並行掃描。
• Global Index (全局)：跨分區唯一性約束 (PK/UK)、OLTP 高頻單行訪問需避免分區剪枝開銷。**維護成本高**：分區操作導致 Global Index UNUSABLE，需 UPDATE GLOBAL INDEXES 子句或事後重建。

-- Local Index 範例
CREATE INDEX idx_sales_cust_id ON sales_orders(cust_id) LOCAL
( PARTITION p_before_2020 TABLESPACE idx_hist,
  PARTITION FOR (DATE '2023-01-01') TABLESPACE idx_curr );

-- Global Index 範例 (PK)
CREATE UNIQUE INDEX pk_sales_order_id ON sales_orders(order_id) GLOBAL
PARTITION BY HASH (order_id) PARTITIONS 16;

4. 分區維護自動化腳本 (每月執行)

DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  -- 1. 新增下月分區 (INTERVAL 自動處理，此處為演示手動邏輯)
  -- 2. 歸檔 13 個月前分區：壓縮 + 移至歷史表空間 + 唯讀
  FOR p IN (SELECT partition_name, high_value 
            FROM user_tab_partitions 
            WHERE table_name = 'SALES_ORDERS' 
              AND high_value < ADD_MONTHS(SYSDATE, -13)) LOOP
    v_sql := 'ALTER TABLE sales_orders MOVE PARTITION ' || p.partition_name || 
             ' TABLESPACE hist_data COMPRESS FOR QUERY HIGH ONLINE';
    EXECUTE IMMEDIATE v_sql;
    EXECUTE IMMEDIATE 'ALTER TABLE sales_orders MODIFY PARTITION ' || p.partition_name || ' READ ONLY';
  END LOOP;
  -- 3. 刪除 36 個月前分區 (配合備份驗證)
  FOR p IN (SELECT partition_name 
            FROM user_tab_partitions 
            WHERE table_name = 'SALES_ORDERS' 
              AND high_value < ADD_MONTHS(SYSDATE, -36)) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE sales_orders DROP PARTITION ' || p.partition_name || ' UPDATE GLOBAL INDEXES';
  END LOOP;
END;
/

支柱二：索引重建 —— 回收空間、消除碎片、恢復聚簇因子

1. 何時重建索引？

2. 重建 vs 重組 (Coalesce) 選擇

3. 智能索引維護 Job (每週低峰執行)

BEGIN
  FOR idx IN (SELECT index_name, table_name, partition_name
              FROM user_ind_partitions 
              WHERE status = 'UNUSABLE'
              UNION ALL
              SELECT i.index_name, i.table_name, NULL
              FROM user_indexes i
              JOIN user_tables t ON i.table_name = t.table_name
              WHERE i.index_type != 'LOB'
                AND i.status = 'VALID'
                AND t.num_rows > 10000
                AND (SELECT pct_used FROM index_stats WHERE name = i.index_name) < 50) LOOP
    
    IF idx.partition_name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD PARTITION ' || idx.partition_name || ' ONLINE PARALLEL 4';
    ELSE
      -- 判斷是否需 REBUILD 或 COALESCE
      EXECUTE IMMEDIATE 'ANALYZE INDEX ' || idx.index_name || ' VALIDATE STRUCTURE';
      -- 邏輯簡化：碎片嚴重 REBUILD，輕度 COALESCE
      EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD ONLINE PARALLEL 4';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || idx.index_name || NVL('.'||idx.partition_name, ''));
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

支柱三：段碎片整理 —— 消除多區段、回收空間、提升掃描效率

1. 碎片形成機制

• 段由多個 **Extent (區段)** 組成，Extent 由連續塊組成。
• 插入/更新導致行遷移/鏈接、Extent 分配不連續。
• PCTFREE 過小導致行遷移、PCTINCREASE > 0 導致 Extent 大小指數增長、手動分配不連續。

2. 碎片檢測與治理標準

-- 核心檢測：Extent 數 > 10 且 總大小 > 100MB 的段
SELECT owner, segment_name, segment_type, tablespace_name, 
       extents, bytes/1024/1024 MB
FROM dba_segments
WHERE extents > 10 AND bytes > 100*1024*1024
ORDER BY extents DESC;

-- 方案一：移動表 (需鎖表，維護窗口)
ALTER TABLE fragmented_table MOVE TABLESPACE target_ts 
  PCTFREE 10 COMPRESS FOR OLTP PARALLEL 8;
-- 同步重建所有索引
ALTER INDEX idx_on_fragmented REBUILD ONLINE PARALLEL 8;

-- 方案二：Exp/Imp (邏輯重組，含壓縮、統計信息重收集)
exp system/pass file=frag.dmp tables=frag_table compress=y grants=y indexes=y
imp system/pass file=frag.dmp tables=frag_table commit=y buffer=64000 full=y
-- 再 DROP 原表、RENAME 新表

3. ASSM (自動段空間管理) + 統一區段大小 (1M) —— 現代最佳實踐

CREATE TABLESPACE data_1m 
  DATAFILE '+DATA' SIZE 100G AUTOEXTEND ON NEXT 10G MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE  -- 1M 統一區段 (11g+ 默認)
  SEGMENT SPACE MANAGEMENT AUTO;        -- ASSM，位圖管理自由空間，無 PCTFREE/PCTUSED/PCTINCREASE 煩惱

效果：自動管理自由列表、消除 Extent 碎片、支援 Segment Shrink、並發插入性能大幅提升。

支柱四：自由區間合併 —— 防止表空間「碎片化無法分配大區段」

1. 問題本質

• 表空間自由空間由多個不連續的 **Free Extents** 組成。
• 段刪除/截斷釋放的 Extent **不會自動與相鄰合併** (除非 PCTINCREASE > 0 觸發 SMON 合併，但已廢棄)。
• 大表需分配大 Extent (如 64MB) 時，雖然總自由空間足夠，但無單個足夠大的連續 Extent → **ORA-01653/01654 分配失敗**。

2. 解決方案對比

3. 監控告警指標

-- 1. 最大自由區段 vs 總自由空間比率
SELECT tablespace_name, 
       MAX(bytes)/1024/1024 max_free_mb,
       SUM(bytes)/1024/1024 total_free_mb,
       ROUND(MAX(bytes)/SUM(bytes)*100, 2) pct_max_free
FROM dba_free_space
GROUP BY tablespace_name
HAVING ROUND(MAX(bytes)/SUM(bytes)*100, 2) < 50;  -- 最大區段佔比 < 50% 告警

-- 2. 連續 3 次檢測最大自由區段遞減趨勢 → 預警碎片化加劇

統一維護調度框架 (DBMS_SCHEDULER 範例)

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'DAILY_SPACE_HEALTH_CHECK',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN check_space_fragmentation; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=30',
    enabled         => TRUE,
    comments        => '每日 02:30 檢測表空間碎片、段碎片、索引碎片，發送報告'
  );
  
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'WEEKLY_INDEX_REBUILD',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN smart_index_rebuild; END;',
    start_date      => NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY') + 3/24,  -- 週日 03:00
    repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=3',
    enabled         => TRUE
  );
  
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONTHLY_PARTITION_MAINTENANCE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN partition_maintenance; END;',
    start_date      => TRUNC(SYSDATE, 'MM') + 1 + 2/24,  -- 每月 1 號 02:00
    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=2',
    enabled         => TRUE
  );
END;
/

結語

Oracle 存儲層健康體系的核心是 **「分區隔離變化、索引定期重組、段空間 ASSM 化、自由區間統一大小」** 四大支柱。將維護動作封裝為參數化 PL/SQL、納入 DBMS_SCHEDULER 自動化調度、建立關鍵指標 (Extent 碎片率、索引 PCT_USED、最大自由區段佔比、分區歸檔完成率) 告警儀表盤，實現從「人工救火」到「系統自愈」的轉變。記住：空間管理不是一次性配置，而是持續治理的過程。每一次分區歸檔、索引重建、表空間合併，都是在為下一次高併發寫入鋪平道路。