Oracle 效能調優三板斧：分區表、索引重建、段/區碎片整理實戰

前言

大型 Oracle 資料庫效能下降常源於三大根因：大表全掃描、索引膨脹、儲存碎片。本文提供分區表設計、索引重建策略、段與自由區碎片整理的完整實操指南，附 DDL 範例與自動化建議。

一、分區表：將大表物理拆分、邏輯統一

1. 範圍分區範例 (按 DEPTNO)

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
);

優勢：分區裁剪、獨立備份/恢復、分區級匯入匯出、分區交換載入。

2. 局部索引 (Local Index) —— 推薦預設

CREATE INDEX emp_deptno_idx 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 ... MOVE PARTITION 自動同步）
• 全域索引僅在「跨分區唯一性約束」時使用

3. 分區維護常用命令

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

-- 刪除分區 (數據同步刪除)
ALTER TABLE employee DROP PARTITION part1;

-- 交換分區 (極速載入/歸檔)
ALTER TABLE employee EXCHANGE PARTITION part2 WITH TABLE staging_emp INCLUDING INDEXES;

-- 移動分區 (換表空間/壓縮)
ALTER TABLE employee MOVE PARTITION part3 TABLESPACE part3_ts COMPRESS;

-- 截斷分區 (保留結構)
ALTER TABLE employee TRUNCATE PARTITION part4;

二、索引重建：回收被刪除項目佔用的空間

1. 為何需要重建？

頻繁 DELETE/INSERT 導致索引葉塊產生大量「已刪除標記」項目，空間不釋放，掃描效率下降，High Water Mark 持續推高。

2. 重建命令 (ONLINE 不阻塞 DML)

ALTER INDEX emp_deptno_idx REBUILD ONLINE;
-- 或指定表空間、壓縮
ALTER INDEX emp_deptno_idx REBUILD ONLINE TABLESPACE idx_ts COMPRESS 1;

3. 自動化排程建議

• 識別高活躍表：SELECT * FROM dba_indexes WHERE last_analyzed < SYSDATE - 7
• 低峰期 (如 02:00) 執行重建作業
• 監控 V$SESSION_LONGOPS 確認進度

三、段碎片整理：消除多 Extent 碎片

1. 判斷標準

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

Extent 數 ≥ 10 且效能異常者優先處理。

2. 兩種整理法

四、自由區碎片整理：合併相鄰免費空間

1. 原理

段釋放後形成自由區，若 PCTINCREASE = 0，SMON 不自動合併，導致「總免費空間大但最大連續自由區小」的假象，新 Extent 分配失敗。

2. 強制合併

ALTER TABLESPACE users COALESCE;
-- 或特定數據文件
ALTER DATABASE DATAFILE '/u01/oradata/users01.dbf' RESIZE 2G; -- 觸發合併

3. 理想模型

• 每個段僅 1 Extent (或少量大 Extent)
• 全表空間自由空間聚合為 1 個巨大連續自由區
• 設定 PCTINCREASE > 0 (如 10) 讓 SMON 定期自動合併

五、總結與自動化建議

1. 分區表：大表 (>100GB) 強制分區，配合局部索引
2. 索引重建：每週低峰期 ONLINE REBUILD 高變動表索引
3. 段碎片：月度掃描 DBA_SEGMENTS，CTAS 整理 Extents > 20 的段
4. 自由區：所有表空間設 PCTINCREASE=10，每季執行 COALESCE
5. 監控儀表板：將上述指標納入 Grafana/Prometheus 告警

持續執行「三板斧」，可將大型 Oracle 系統效能穩定在最佳狀態，延緩硬體擴容週期。