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

資料庫效能退化多源於「I/O 熱點、索引膨脹、段碎片、自由空間碎片」四大根因。本文系統闡述 Oracle 透過分區表、索引重建、段碎片整理、自由區塊合併四大技術支柱，維持長期穩定效能的實作細節與排程策略。

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

1. 範圍分區建表示例

CREATE TABLE employee (
    emp_no VARCHAR2(10) PRIMARY KEY,
    name   VARCHAR2(30),
    dept_no NUMBER(2)
)
PARTITION BY RANGE (dept_no) (
    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. 本地分區索引（推薦）

CREATE INDEX employee_dept_no ON employee(dept_no) 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 INDEX ... REBUILD PARTITION），不影響其他分區可用性。
• 全域索引：跨分區唯一性約束時使用，但維護複雜（Split/Merge 分區需 UPDATE GLOBAL INDEXES）。

3. 分區維護常用指令

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

-- 刪除分區（資料同步刪除）
ALTER TABLE employee DROP PARTITION part1 UPDATE GLOBAL INDEXES;

-- 截斷分區（保留結構，極快）
ALTER TABLE employee TRUNCATE PARTITION part1;

-- 移動分區（換表空間/壓縮）
ALTER TABLE employee MOVE PARTITION part2 TABLESPACE new_ts COMPRESS;

-- 分割分區
ALTER TABLE employee SPLIT PARTITION part4 AT (35) INTO (
    PARTITION part4a TABLESPACE part4a_ts,
    PARTITION part4b TABLESPACE part4b_ts
);

-- 合併分區
ALTER TABLE employee MERGE PARTITIONS part1, part2 INTO PARTITION part12 TABLESPACE part12_ts;

二、索引重建：回收刪除空間、消除碎片

頻繁 DML 導致索引葉子塊產生大量「已刪除但未回收」項目，索引體積膨脹、掃描效率下降。

重建策略

• 線上重建（10g+）：ALTER INDEX idx_name REBUILD ONLINE; 不鎖表，適合 7x24 系統。
• 平行重建：ALTER INDEX idx_name REBUILD PARALLEL 4; 大索引加速。
• 定期排程：針對高活躍表（日均變更 > 10%），每週維護窗口重建一次。
• 監控指標：INDEX_STATS 視圖的 HEIGHT、LF_BLKS、DEL_LF_ROWS / LF_ROWS > 20% 即考慮重建。

自動化腳本範例

BEGIN
  FOR rec IN (
    SELECT index_name, owner
    FROM   dba_indexes
    WHERE  owner = 'SCOTT'
    AND    index_type = 'NORMAL'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '||rec.owner||'.'||rec.index_name||' REBUILD ONLINE PARALLEL 4';
  END LOOP;
END;
/

三、段碎片整理：讓段只佔一個區塊

段由多個不連續區塊組成，超過 10 個區塊即視為碎片化，導致：

• 全表掃描多次「區塊跳躍」I/O。
• 空間管理複雜，易發生「無法分配區塊」錯誤。

碎片偵測

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

兩種整理方案

1. CTAS 重建法（停機窗口允許時）：

   CREATE TABLE new_t NOLOGGING PARALLEL 4 AS SELECT * FROM old_t;
   DROP TABLE old_t;
   RENAME new_t TO old_t;
   -- 重建索引、授權、約束
   

2. Export/Import 法（跨平台/版本/字元集遷移同用）：

   exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y tables=(T1,T2)
   -- 刪除原表
   imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y
   

   compress=Y 讓匯出時重設儲存參數，匯入時自動合併為單一區塊。

四、自由區塊合併：消除表空間「孔洞」

段釋放後形成自由區塊，若 PCTINCREASE = 0，SMON 不會自動合併相鄰自由區塊，導致：

• 大區塊分配請求失敗（ORA-01652/01653），即使總自由空間足夠。
• 空間利用率低，檔案過早自動擴展。

強制合併指令

-- 表空間層級
ALTER TABLESPACE users COALESCE;

-- 特定資料檔
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/users01.dbf' RESIZE 2G;  -- 觸發隱性合併

理想表空間模型

• 每個段僅佔 1 個區塊（INITIAL = NEXT，PCTINCREASE = 0，MINEXTENTS = 1）。
• 所有自由空間聚合為 1 個超大連續區塊。
• 定期 COALESCE + 監控 DBA_FREE_SPACE 最大區塊大小佔比。

五、整合排程建議

將上述四大支柱納入「資料庫健康度儀表板」，設定閾值告警（如：最大自由區塊 < 100MB、索引刪除比 > 20%、段區塊數 > 50），從被動救火轉為主動預防，讓 Oracle 效能真正實現「長青」。