Oracle 資料庫效能優化四大支柱：分區、索引重建、段碎片整理與自由區塊合併實戰指南

前言：大型資料庫效能維護的系統性方法

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 支援：ADD、DROP、EXCHANGE、MOVE、MODIFY、RENAME、SPLIT、TRUNCATE 分區

二、索引重建 — 回收刪除記錄佔用的空間

問題根源

• 頻繁 DELETE/INSERT 導致索引空間持續增長
• 刪除記錄的索引項空間無法自動重用（標記為已刪除但未釋放）
• 表記錄數不變，索引卻膨脹

解決方案

ALTER INDEX index_name REBUILD;

• 建立定期批次作業，離峰時段重建高活躍表索引
• 避免與業務應用衝突
• 及時回收未使用空間，提升空間利用率

三、段碎片整理 — 消除多區段擴展帶來的效能損耗

碎片形成機制

• 段由區段組成，區段由相鄰 Oracle 區塊組成
• 區段滿後獲取新區段，不要求相鄰
• 擴展持續至表空間耗盡或達區段數上限
• 碎片過多 → 影響運行、引發空間管理問題

識別碎片段

SELECT owner, segment_name, segment_type, extents
FROM dba_segments
WHERE extents >= 10
ORDER BY extents DESC;

兩種整理方法

1. 重建表法：正確儲存參數建新表 → 插入舊資料 → 刪除舊表
2. Export/Import 法（推薦）：

   
   -- 匯出 (compress=Y 修改儲存參數合併區段)
   exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y tables=(T1,T2)
   
   -- 刪除原表
   DROP TABLE T1; DROP TABLE T2;
   
   -- 匯入
   imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y
   

四、自由區塊碎片合併 — 確保大區段分配可用

問題現象

• 段釋放後形成自由區段，不自動與相鄰自由區段合併
• 介面線永久存在，除非 PCTINCREASE != 0 由 SMON 定期合併
• 大區段請求時不主動合併，導致「明明有空間卻分配失敗」

強制合併命令

ALTER TABLESPACE tablespace_name COALESCE;

理想表空間模型

• 每個物件佔單一區段
• 所有有效自由空間集中在單一巨大連續區段
• 最小化空間循環尋找，最大化自由空間使用率

綜合維護建議

1. 建立每週/每月自動化維護作業：索引重建 → 段碎片檢查 → 表空間合併
2. 監控關鍵指標：區段數、表空間使用率、自由區段數量/大小分布
3. 新建表預估增長，設定適當 INITIAL、NEXT、PCTINCREASE
4. 大表採用分區 + 本地索引，從架構層面預防碎片