萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 表碎片成因深度解析與線上重組最佳實踐

表碎片是導致全表掃描效能下降、高水位線 (HWM) 虛高、備份窗口延長的隱形殺手。本文從數據塊層面剖析碎片產生機制,對比 Shrink Space、Online Redefinition、Partition Exchange、CTAS 等重組方案優劣,提供生產環境零停機重組標準作業流程 (SOP)。

碎片類型與成因機制

1. 表空間級碎片 (Tablespace Fragmentation)

  • 成因:不同大小的區塊 (Extents) 交錯分布、字典管理表空間 (DMT) 缺乏統一區塊大小、頻繁 DROP/CREATE 表導致自由空間破碎。
  • 識別:DBA_FREE_SPACE 顯示大量小塊自由空間,最大連續自由區塊遠小於總自由空間。
  • 解決:遷移至本地管理表空間 (LMT) + 均勻區塊大小 (UNIFORM SIZE) 或 AUTOALLOCATE。

2. 段級碎片 / 高水位線虛高 (Segment Fragmentation / High Water Mark)

  • 成因:大量 DELETE 操作釋放區塊但不降低 HWM、頻繁 UPDATE 導致行遷移 (Row Migration/Chaining)、TRUNCATE 未釋放空間 (DROP STORAGE 預設)、Direct Path Load 後大量刪除。
  • 識別指標:
    • BLOCKS (DBA_TABLES) ≫ EMPTY_BLOCKS + AVG_ROW_LEN * NUM_ROWS / BLOCK_SIZE
    • CHAIN_CNT / AVG_SPACE 異常偏高
    • 全表掃描邏輯讀遠大於實際數據量

重組方案對比與選型

方案鎖定模式適用表大小索引/約束/觸發器處理臨時空間版本要求
ALTER TABLE ... SHRINK SPACE [COMPACT] + CASCADE行級鎖 (短暫排他鎖)< 100GB自動維護極少10g+,需啟用 Row Movement
DBMS_REDEFINITION (Online Redefinition)僅同步切換瞬間排他鎖任意大小 (TB 級)自動同步/手動重建約 1.2x 表大小9i+ (11g+ 支援並行 DML 同步)
Partition Exchange + Subpartition元數據操作 (毫秒級)已分區表需預建目標分區索引無額外8i+
CTAS (CREATE TABLE AS SELECT) + RENAME全程排他鎖< 50GB需手動重建所有依賴物件1x 表大小所有版本
EXP/IMP 或 EXPDP/IMPDP (TRUNCATE+INSERT)全程排他鎖中小表參數控制轉儲檔空間所有版本

生產環境零停機重組 SOP (以 DBMS_REDEFINITION 為例)

-- 1. 檢查表是否可重定義 (需主鍵或偽主鍵)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA', 'BIG_TABLE', DBMS_REDEFINITION.CONS_USE_PK);

-- 2. 建立中間表 (相同結構,可調整存儲屬性/分區/壓縮)
CREATE TABLE schema.big_table_int 
  TABLESPACE data_new 
  COMPRESS FOR OLTP 
  AS SELECT * FROM schema.big_table WHERE 1=0;

-- 3. 複製依賴物件:索引、約束、觸發器、權限、統計資訊
-- 使用 DBMS_METADATA.GET_DDL 批量生成 DDL,調整 TABLESPACE 後在中間表創建

-- 4. 啟動重定義 (並行 DML 同步增量變更)
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
  uname => 'SCHEMA',
  orig_table => 'BIG_TABLE',
  int_table => 'BIG_TABLE_INT',
  options_flag => DBMS_REDEFINITION.CONS_USE_PK);

-- 5. 定期同步增量 (業務低峰期執行,縮短最終切換窗口)
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA', 'BIG_TABLE', 'BIG_TABLE_INT');

-- 6. 完成重定義 (極短鎖定,通常 < 5 秒)
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA', 'BIG_TABLE', 'BIG_TABLE_INT');

-- 7. 驗證並清理
-- 對比行數、約束狀態、索引可用性、統計資訊
DROP TABLE schema.big_table_int PURGE;

碎片預防長效機制

  1. 表空間規劃:統一採用 LMT + AUTOALLOCATE,大表預分配大區塊 (INITIAL 100M+)。
  2. 分區策略:按時間/業務鍵範圍分區,歷史分區 READ ONLY + 壓縮,定期 PURGE 而非 DELETE。
  3. DML 模式優化:批量刪除改用分區截斷或 DELETE /*+ PARALLEL(8) */ + 定期 SHRINK SPACE COMPACT。
  4. 監控體系:納入每周巡檢,關注 SEGMENT_ADVISOR 建議、HWM 增長趨勢、鏈式行比率。
copyright © 萬盛學電腦網 all rights reserved