您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

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

編輯：Oracle 資料庫基礎

大型 Oracle 資料庫隨時間運行，空間碎片、索引膨脹、段擴展失控會嚴重拖垮查詢效能與空間利用率。本文系統闡述四大核心優化手段——分區、索引重建、段碎片整理、自由區塊合併，附實戰語法與排程策略，助你構建精簡高效的存儲體系。

一、分區 —— 大表管理的瑞士軍刀

將邏輯大表按範圍、列表、雜湊、複合策略物理拆分到不同 Tablespace，實現：

查詢裁剪： 僅掃描相關分區，避免全表掃描。
維護隔離： 單分區備份/恢復/重建索引/移動，不影響其他分區。
I/O 分散： 分區分佈不同磁碟，均衡競爭。
資料生命週期： 歷史分區歸檔/壓縮/唯讀/丟棄，極低成本。

範圍分區範例

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

原則： 局部索引 1:1 對應表分區，維護簡單、分區裁剪有效、支援分區獨立操作。全局索引僅在需跨分區強制唯一性時使用。

分區管理命令

ALTER TABLE ... ADD/DROP/EXCHANGE/MOVE/MODIFY/RENAME/SPLIT/TRUNCATE PARTITION 靈活應對業務變化。

二、索引重建 —— 回收膨脹空間、恢復掃描效率

頻繁 DML 導致索引葉塊分裂、刪除標記堆積、PCT_USED 下降、Clustering Factor 惡化。現象：Index Fast Full Scan 讀取大量空塊、高度增加、查詢變慢。

重建策略

ALTER INDEX idx_name REBUILD [ONLINE] [TABLESPACE new_ts] [PARALLEL n];

ONLINE： 10g+ 支援，DML 不阻塞，維護窗口零停機。
排程： 針對高活躍表，每週/月低峰期自動重建 (DBMS_SCHEDULER)。
監控指標： INDEX_STATS 中 HEIGHT、LF_BLKS、DEL_LF_ROWS/LF_ROWS > 20% 觸發重建。

三、段碎片整理 —— 消除 Extent 交織、縮減 Tablespace 壓力

成因： 同一 Tablespace 內靜態表與動態表混雜、Extent 尺寸不一、動態擴展導致 Extent 交織、空間無法釋放回 OS。

判斷標準

查詢 DBA_SEGMENTS，EXTENTS >= 10 且非預期者為碎片候選。

兩種整理法

CTAS 重建 (推薦) ：

CREATE TABLE new_t TABLESPACE target_ts AS SELECT * FROM old_t ORDER BY key_col;
DROP TABLE old_t;
RENAME new_t TO old_t; -- 重建索引、授權、同義詞

可同時調整儲存參數、重排序資料 (Row Resquencing)、壓縮。

Export/Import (傳統) ：

exp system/manager file=exp.dmp compress=y grants=y indexes=y tables=(T1,T2)
-- drop tables --
imp system/manager file=exp.dmp commit=y buffer=64000 full=y

COMPRESS=Y 合併 Extent 為單一 Initial Extent。注意：Imp 時可重新指定儲存參數。

四、自由區塊合併 —— 避免 Tablespace 碎片化導致大Extent分配失敗

現象： 相鄰 Free Extent 不自動合併 (預設 PCTINCREASE=0 或非 ASSM)，大量小洞導致「總空間夠但找不到足夠大連續 Extent」報錯 ORA-01652。

解決方案

設定 PCTINCREASE > 0 (字典管理) 或使用 ASSM (LMT + SEGMENT SPACE MANAGEMENT AUTO)： SMON 背景進程定期自動合併相鄰 Free Extent。
手動合併： ALTER TABLESPACE ts_name COALESCE; 強制合併相鄰自由區塊。
預防： 靜態物件集中放入唯讀/固定大小 Tablespace；動態物件獨立 Tablespace、啟用 ASSM、統一 Extent 大小 (Uniform Size)。