Oracle 表空間碎片整理與效能調優：COALESCE、PCTINCREASE、SHRINK SPACE 綜合實戰

碎片成因與危害

• 頻繁 `INSERT/DELETE/UPDATE` 導致 Extent 碎片化、空閒空間分散。
• HWM 不下降，全表掃描讀取大量空塊，I/O 放大、緩存污染。
• Extent 分配/釋放頻繁引發 `ST` 鎖爭用、字典緩存壓力。

四大整理手段對比

PCTINCREASE 深度解析

• 0**：SMON **不** 自動合併空閒 Extent，必須手工 `COALESCE`。
• >0 (建議 1)**：SMON 定期 (約 12 小時) 自動合併相鄰空閒 Extent，**防止碎片累積**。
• 大數值 (如 50)**：Extent 大小呈幾何級數增長 (1M → 1.5M → 2.25M...)，極易造成空間浪費與分配失敗，**嚴禁用於生產表空間**。
• 與 AUTOEXTEND 無關**：表空間自動擴展由 `AUTOEXTEND ON NEXT n` 控制，線性增長，不受 PCTINCREASE 影響。

實戰操作序列 (推薦組合拳)

-- 1. 表空間層面：合併碎片 + 啟用自動合併
ALTER TABLESPACE users COALESCE;
ALTER TABLESPACE users STORAGE (PCTINCREASE 1);

-- 2. 核心大表層面：分批 SHRINK (配合維護窗口)
-- 2.1 先 COMPACT 整理塊內空間 (無 HWM 鎖，業務高峰可做)
ALTER TABLE big_table ENABLE ROW MOVEMENT;
ALTER TABLE big_table SHRINK SPACE COMPACT;

-- 2.2 維護窗口正式收縮 (短暫鎖定)
ALTER TABLE big_table SHRINK SPACE;
ALTER TABLE big_table DISABLE ROW MOVEMENT;

-- 3. 同步收縮索引
ALTER INDEX big_table_pk SHRINK SPACE;
-- 其他索引同理

-- 4. 驗證效果
SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space WHERE tablespace_name='USERS' GROUP BY tablespace_name;
-- 觀察 Free Space 回升、最大連續 Free Extent 變大

效能調優關聯動作

1. 統計資訊同步更新：Shrink 後必須 `DBMS_STATS.GATHER_TABLE_STATS(..., CASCADE=>TRUE)`，否則 CBO 仍按舊統計產生錯誤計畫。
2. 檢查連鎖行：`ANALYZE TABLE t LIST CHAINED ROWS`，若大量鏈行需 `ALTER TABLE t MOVE` (離線) 或重建。
3. 調整 PCTFREE：重建/收縮時可同步 `ALTER TABLE t PCTFREE 20` 預留更新空間，減少未來行遷移。

避坑清單