Oracle 效能優化四大支柱：分區、索引重建、段碎片整理、自由區塊合併的系統化實踐

前言：效能優化不是單點突破，是系統工程

大型資料庫中 80% 空間常被 20% 表佔用。針對這些核心對象，從邏輯分區、索引維護、段碎片消除、自由區塊合併四個維度建立常態化優化機制，是支撐 TB/PB 級資料庫穩定高效運行的基石。本文提供標準化實施框架、關鍵 SQL、排程策略與效果驗證方法。

一、分區策略：大表治理的「分而治之」藝術

1. 分區表建立範例 (範圍分區 + 表空間隔離)

CREATE TABLE employee (
  emp_no    VARCHAR2(10) PRIMARY KEY,
  name      VARCHAR2(30),
  dept_no   NUMBER(2)
)
PARTITION BY RANGE (dept_no) (
  PARTITION part1 VALUES LESS THAN (11) TABLESPACE part1_ts,
  PARTITION part2 VALUES LESS THAN (21) TABLESPACE part2_ts,
  PARTITION part3 VALUES LESS THAN (31) TABLESPACE part3_ts,
  PARTITION part4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4_ts
);

2. 區域索引 — 分區表的標配

CREATE INDEX employee_dept_idx ON employee(dept_no) LOCAL (
  PARTITION part1 TABLESPACE part1_idx_ts,
  PARTITION part2 TABLESPACE part2_idx_ts,
  PARTITION part3 TABLESPACE part3_idx_ts,
  PARTITION part4 TABLESPACE part4_idx_ts
);

原則：
• 優先使用 LOCAL 索引：分區與索引分區 1:1 對應，支援分區級維護 (TRUNCATE/EXCHANGE/MOVE 自動同步索引分區)
• GLOBAL 索引僅用於：跨分區唯一性約束 (PK/UK 不含分區鍵)、特定高頻點查需求
• 分區剪枝：查詢條件包含分區鍵 → 僅掃描相關分區，效能指數級提升

3. 分區管理常用操作

-- 增加分區
ALTER TABLE employee ADD PARTITION part5 VALUES LESS THAN (41) TABLESPACE part5_ts;

-- 截斷分區 (歸檔/清理歷史數據，秒級)
ALTER TABLE employee TRUNCATE PARTITION part1;

-- 交換分區 (數據載入/歸檔，零數據移動)
ALTER TABLE employee EXCHANGE PARTITION part1 WITH TABLE emp_staging;

-- 移動分區 (跨表空間/壓縮/重建)
ALTER TABLE employee MOVE PARTITION part2 TABLESPACE new_ts COMPRESS FOR OLTP;

-- 合併/分裂分區
ALTER TABLE employee MERGE PARTITIONS part3, part4 INTO PARTITION p34;
ALTER TABLE employee SPLIT PARTITION p34 AT (35) INTO (PARTITION part3, PARTITION part4);

二、索引重建：回收「邏輯刪除」佔用的空間，恢復掃描效能

問題本質

頻繁 DELETE/UPDATE 導致索引葉塊大量「已刪除標記」條目。這些空間不自動回收，只能被相同鍵值復用。結果：索引膨脹、樹高增加、快速全掃描/範圍掃描變慢、緩存命中率下降。

重建策略

-- 單索引重建 (ONLINE 避免鎖表，12c+ 支援)
ALTER INDEX employee_dept_idx REBUILD ONLINE;

-- 分區索引重建 (逐分區，減少鎖衝突)
ALTER INDEX employee_dept_idx REBUILD PARTITION part1 ONLINE;

-- 批次重建腳本 (每日低峰執行)
BEGIN
  FOR idx IN (SELECT index_name, owner FROM dba_indexes 
              WHERE owner = 'APP_SCHEMA' 
                AND index_type != 'LOB'
                AND status = 'VALID') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' REBUILD ONLINE';
      DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || idx.index_name);
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Failed: ' || idx.index_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/
-- 配合 ANALYZE INDEX ... VALIDATE STRUCTURE 獲取 HEIGHT、LF_BLKS、DEL_LF_ROWS 判斷是否需重建

判斷指標

• HEIGHT > 3 (或 > 4 視資料量) → 考慮重建
• DEL_LF_ROWS / LF_ROWS > 20% → 碎片嚴重
• LF_BLKS 顯著大於理論值 → 空間浪費

三、段碎片整理：消除「多區段」帶來的 I/O 放大與空間管理惡夢

碎片成因

段由多個不連續區組成。區不連續 → 讀取需多次磁頭移動/搜尋 → I/O 效能下降。區數達上限 (MAXEXTENTS) → 無法擴展 → ORA-01631/01632。

識別高碎片段

SELECT owner, segment_name, segment_type, extents, bytes/1024/1024 size_mb
FROM dba_segments
WHERE extents > 100  -- 經驗閾值
ORDER BY extents DESC;

兩大整理方案

CREATE TABLE new_t TABLESPACE new_ts AS SELECT * FROM old_t;
DROP TABLE old_t;
RENAME new_t TO old_t;

exp system/pwd file=exp.dmp compress=y grants=y indexes=y tables=(t1,t2)
-- compress=y 導出時調整存儲參數 (INITIAL=總大小, NEXT=0)
DROP TABLE t1, t2;
imp system/pwd file=exp.dmp commit=y buffer=64000 full=y

線上重定義替代方案 (DBMS_REDEFINITION)

零停機重建段，參考文章 94509。適用於 7×24 核心表。

四、自由區塊合併：讓表空間「大塊可用」，避免空間碎片化導致的分配失敗

機制原理

• 段釋放區 → 區標記為「自由區塊」
• 相鄰自由區塊不自動合併 (除非 PCTINCREASE > 0 由 SMON 定期合併)
• 分配新區時，不主動合併相鄰自由區塊，除非找不到足夠大的單一自由區塊
• 結果：大量小自由區塊碎片化 → 申請大區失敗 (ORA-01652/01653) → 實際有空間卻無法分配

強制合併命令

-- 合併指定表空間所有相鄰自由區塊
ALTER TABLESPACE users COALESCE;

-- 監控合併效果
SELECT tablespace_name, 
       COUNT(*) chunk_count,
       ROUND(MAX(bytes)/1024/1024,2) max_chunk_mb,
       ROUND(SUM(bytes)/1024/1024,2) total_free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY max_chunk_mb;

最佳實踐

1. 預設表空間 PCTINCREASE 設為 > 0 (如 1 或默認 50)，啟用 SMON 自動合併
2. 定期手動 COALESCE：每月維護窗口執行，針對高碎片表空間
3. 監控指標：CHUNK_COUNT > 1000 或 MAX_CHUNK_MB < 100 → 告警
4. 局部管理表空間 (LMT) + AUTOALLOCATE：10g+ 預設，區大小系統管理 (64K/1M/64M)，大幅減少碎片

五、常態化優化作業排程表

總結

分區治理大表邏輯、索引重建恢復存取路徑、段重建消除物理碎片、自由區塊合併保障空間分配 — 四大支柱缺一不可。將其納入常態化排程、標準化腳本、指標化監控、文檔化複盤，即可構建「自我優化」的資料庫生態。請從本月開始，為你的核心表空間建立「碎片健康檔案」，讓每一次優化都有據可依、有效可驗。