Oracle 表空間碎片管理與儲存參數調優實戰 (含 SAFE 原則與 PCTFREE 解析)

核心概念速覽

• 表空間：邏輯儲存容器，由一或多個資料檔組成。
• 區塊：最小 I/O 單位，大小由 DB_BLOCK_SIZE 決定 (通常 8K)。
• Extent (區段/區塊群)：連續區塊集合，段擴展的基本單位。
• Segment (段)：表、索引、LOB 等物件的儲存實體，由多個 Extent 組成，隸屬單一表空間。

表空間碎片成因與危害

同一表空間內多個段的 Extent 交錯分布，隨著建表/刪表/截斷，原本連續的自由空間被切碎。雖 DBA_FREE_SPACE 總和顯示數百 MB，但找不到足夠大的連續 Extent 滿足 NEXT 擴展，導致 ORA-01653 甚至 ORA-01654。

SAFE 原則：從根源杜絕 Extent 碎片

Bhaskar Himatsingka & Juan Loaiza 提出，後被 Locally Managed Tablespace (LMT) 內建實現。核心規則：

單一 Extent 迷思

舊文獻建議 EXPORT COMPRESS=Y 將所有資料壓縮進單一 Extent。實測：

• 唯有「全段掃描 + 物理連續儲存」才有優勢。
• 現代儲存皆 RAID 0+1/5 條帶化，邏輯單 Extent 在物理上已分散多磁碟。
• Oracle 管理數百 Extent 極輕量，無效能損耗。
• 強制單 Extent 反而製造表空間碎片，得不償失。

段層級空間參數：PCTFREE / PCTUSED

CREATE TABLE t (
  id NUMBER,
  data VARCHAR2(2000)
) PCTFREE 20 PCTUSED 40 TABLESPACE users;

PCTFREE (預留更新空間)

• 區塊達到 (100 - PCTFREE)% 滿度後，不再接受新 INSERT，留給 UPDATE 原地擴展。
• 若 UPDATE 導致行長度暴增超出區塊剩餘空間 → Row Migration (行遷移)：行搬移至新區塊，原處留 Forwarding Address，讀取需雙 I/O。
• 建議值：
  • 唯讀/極少 UPDATE：PCTFREE 5~10
  • 頻繁 UPDATE、欄位可變長 (VARCHAR2, LOB)：PCTFREE 15~25
  • 不知情況：PCTFREE 10 (預設) 或 20 較保險

PCTUSED (重用門檻)

• 區塊經 DELETE 釋放空間，使用率跌破 PCTUSED% 才重新列入 FREELIST 供 INSERT 使用。
• 過高 → 空間碎片化嚴重；過低 → 頻繁在稀疏區塊插入，增加尋找成本。
• 典型搭配：PCTFREE 20 / PCTUSED 40 (預留 20%，跌至 40% 才重用)。

ASSM (Automatic Segment Space Management) 取代手動設定

LMT + SEGMENT SPACE MANAGEMENT AUTO (10g 預設) 使用位圖追蹤區塊可用空間，完全忽略 PCTFREE/PCTUSED，自動最佳化插入位置，消除 Row Migration 風險，**強烈建議所有新建表空間啟用 ASSM**。

實務檢查腳本

-- 1. 表空間使用率與最大連續自由空間
SELECT tablespace_name,
       ROUND(100 * (1 - free_mb / total_mb), 2) AS pct_used,
       max_free_mb
FROM (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS total_mb
  FROM dba_data_files GROUP BY tablespace_name
) a
JOIN (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS free_mb,
         MAX(bytes)/1024/1024 AS max_free_mb
  FROM dba_free_space GROUP BY tablespace_name
) b USING (tablespace_name)
ORDER BY pct_used DESC;

-- 2. 段 Extent 計數 TOP 20 (關注 > 1024)
SELECT owner, segment_name, segment_type, extents
FROM dba_segments
WHERE extents > 500
ORDER BY extents DESC
FETCH FIRST 20 ROWS ONLY;

-- 3. Row Migration 統計 (ANALYZE 後查詢)
SELECT chain_cnt, avg_row_len, blocks
FROM dba_tables
WHERE owner = 'APP' AND chain_cnt > 0
ORDER BY chain_cnt DESC;

總結

1. 全面採用 LMT + ASSM + UNIFORM SIZE，棄用手動 Storage 子句。
2. 依物件大小分級建立 3~4 個表空間 (Small/Medium/Large/LOB)，物件出生即歸屬正確層級。
3. 定期監控 max_free_mb、段 Extent 數、Row Migration，提前擴容或移動。
4. 大表採用分區表 + 本地索引，配合 ALTER TABLE ... MOVE PARTITION 線上維護。