Oracle 效能防護四大支柱：分區、索引重建、段碎片整理、自由區段合併

系統化解決大型資料庫空間浪費與查詢退化的核心策略

隨著資料增長，大表空間碎片、索引膨脹、段擴散會嚴重拖慢查詢效能。本文深度解析四大防護支柱：範圍分區表/索引、索引重建策略、段碎片診斷與整理、自由區段合併機制，附完整語法、自動化腳本與監控指標。

支柱一：範圍分區 — 物理隔離大表資料

分區表建立

CREATE TABLE employee (
  empno VARCHAR2(10) PRIMARY KEY,
  name  VARCHAR2(30),
  deptno NUMBER(2)
)
PARTITION BY RANGE (deptno) (
  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
);

本地分區索引 (推薦)

CREATE INDEX employee_deptno ON employee (deptno) LOCAL (
  PARTITION part1 TABLESPACE part1_ndx_ts,
  PARTITION part2 TABLESPACE part2_ndx_ts,
  PARTITION part3 TABLESPACE part3_ndx_ts,
  PARTITION part4 TABLESPACE part4_ndx_ts
);

• 本地索引：分區與表分區一一對應，維護簡單、支援分區修剪
• 全域索引：跨分區唯一性約束時使用，維護複雜

分區維護操作

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

-- 合併分區
ALTER TABLE employee MERGE PARTITIONS part1, part2 INTO PARTITION part1_2;

-- 移動分區 (釋放碎片、更換表空間)
ALTER TABLE employee MOVE PARTITION part3 TABLESPACE new_ts UPDATE INDEXES;

-- 截斷分區 (快速清除歷史資料)
ALTER TABLE employee TRUNCATE PARTITION part1 DROP STORAGE;

支柱二：索引重建 — 回收刪除空間、恢復叢集因子

何時重建

• 大量 DELETE/UPDATE 導致索引稀疏 (葉子塊使用率 < 50%)
• 索引高度 (BLEVEL) 增加
• 叢集因子 (CLUSTERING_FACTOR) 接近表塊數，範圍掃描效能差

重建語法

-- 離線重建 (鎖表)
ALTER INDEX idx_name REBUILD;

-- 線上重建 (10g+ 企業版，不鎖表)
ALTER INDEX idx_name REBUILD ONLINE;

-- 指定表空間、壓縮、平行度
ALTER INDEX idx_name REBUILD TABLESPACE idx_ts COMPRESS 2 PARALLEL 4;

自動化排程腳本範例

BEGIN
  FOR idx IN (SELECT index_name FROM dba_indexes 
              WHERE owner = 'APP_OWNER' AND status = 'VALID')
  LOOP
    -- 簡單啟發式：葉子塊使用率低或高度>3
    EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD ONLINE';
  END LOOP;
END;
/

支柱三：段碎片整理 — 消除多區段 (Extent) 分散

碎片診斷查詢

SELECT owner, segment_name, segment_type, extents, bytes/1024/1024 MB
FROM dba_segments
WHERE extents > 10  -- 經驗閾值
  AND owner NOT IN ('SYS','SYSTEM')
ORDER BY extents DESC;

整理方案

支柱四：自由區段合併 — 避免表空間碎片化導致分配失敗

機制原理

• PCTINCREASE ≠ 0 時，SMON 背景程序定期合併相鄰自由區段
• PCTINCREASE = 0 (局部管理表空間預設) 時，不自動合併

手動合併指令

-- 表空間層級合併
ALTER TABLESPACE ts_name COALESCE;

-- 檢查合併前後
SELECT tablespace_name, COUNT(*) free_extents, MAX(bytes) max_free, SUM(bytes) total_free
FROM dba_free_space
GROUP BY tablespace_name;

最佳實務配置

• 採用 局部管理表空間 (LMT) + AUTOALLOCATE，自動管理區段大小，免除 PCTINCREASE 設定
• 統一區段大小 (UNIFORM SIZE 1M/5M/10M) 適用固定增長模式
• 定期監控 DBA_FREE_SPACE：最大自由區段 < 最大物件需求空間 → 預警
• 避免頻繁 DROP/CREATE 表/索引造成自由區段碎片化

整合監控儀表板核心指標

建立「分區隔離、索引重建、段整理、區段合併」四位一體的效能防護體系，讓大型資料庫長期保持最佳狀態。