您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

DB2 數據庫效能調優核心命令全攻略：從執行計劃到並行處理

編輯：DB2 教學

前言

數據庫效能始終是 DBA 與開發人員關注的核心議題。IBM DB2 提供了豐富的命令列工具與配置參數，幫助我們從 SQL 執行計劃、統計信息維護、索引設計、緩衝池管理、表重組到並行處理等多個維度進行深度調優。本文系統整理了實戰中高頻使用的 DB2 效能調優命令，並附帶範例與說明，供您快速查閱與實操參考。

一、查看 SQL 執行計劃

掌握執行計劃是 SQL 調優的起點。DB2 提供兩大主流工具：

1. db2expln（靜態 SQL 包專用）

db2expln -d dbname -c pkgOwner -p pkgName -o expln.out

-d：目標資料庫
-c：套件擁有者
-p：套件名稱
-o：輸出檔案

2. dynexpln（動態 SQL 即時分析）

# 直接指定 SQL 字串
dynexpln -d eos -q "select * from eosmenu" -g -t

# 從檔案讀取 SQL
dynexpln -d eos -f query.sql -o out.txt

關鍵參數：-g 產生圖形化計劃、-t 顯示總成本、-o 指定輸出路徑。

二、統計信息維護：RUNSTATS

優化器依賴準確的統計信息生成最優計劃。常用語法：

# 基本表與索引統計（允許並發讀取）
db2 runstats on table songxn.eosmenu and indexes songxn.ix1, songxn.ix2 allow read access

# 含分佈統計與詳細索引統計
db2 runstats on table songxn.eosmenu with distribution and detailed indexes all

建議：大量資料變更後、重組完成後、定期維護窗口中務必執行 RUNSTATS。

三、優化等級調整

DB2 提供 0、1、2、3、5、7、9 共七個優化等級，預設為 5。等級越高，優化器搜尋空間越大，編譯耗時越久。

實例級：db2 update db cfg for dbname using DFT_QUERYOPT 3
會話級：set current query optimization = 3

OLTP 系統通常 3~5 即可；複雜報表查詢可嘗試 7 或 9。

四、索引策略與管理

1. 建立索引

# 唯一索引
create unique index ind1 on vicky.staff (dept, lastname);

# 包含非鍵值欄位（INCLUDE 欄位不參與排序，僅用於 Index-Only Access）
create unique index ind2 on vicky.employee (empno) include (lastname, salary);

2. 叢集索引

create index ind3 on vicky.staff (dept) cluster;

叢集索引讓資料頁物理順序與索引順序一致，大幅提升範圍掃描效能。

3. 掃描模式對照

模式	說明
Index Scan	走索引樹回表取數據
Full Index Scan (Index Only)	僅掃描索引葉頁，不回表
Relation Scan	全表掃描

4. 索引查詢與建議器

# 查詢表索引
select indname from syscat.indexes where tabname='CUSTOMER';

# 索引建議器
db2advis -d dbname -i query.sql -o advis.out
# 或直接指定 SQL
db2advis -d dbname -s "select * from t1 where c1=?" -o advis.out

五、頁面預留空間

預留空間減少頁分裂，適用於頻繁 UPDATE/INSERT 的表。

# ALTER TABLE 方式（下次 REORG 生效）
alter table vicky.staff pctfree 30;

# LOAD 方式立即生效
load from staff.ixf of ixf modified by pagefreespace=30 replace into vicky.staff;

六、緩衝池與 I/O 優化

1. 建立專用緩衝池

db2 create bufferpool bpname size 1000 pagesize 4k;
alter tablespace tbname bufferpool bpname;

2. 髒頁刷新閾值

當髒頁比例超過 CHNGPGS_THRESH（資料庫配置參數，預設 60%）時，頁面清理器會觸發寫入。

3. 延伸儲存

# 啟用/關閉延伸緩衝池
db2 alter bufferpool ibmdefaultbp extended storage;
db2 alter bufferpool ibmdefaultbp not extended storage;

相關參數：ESTORE_SEG_SZ、NUM_ESTORE_SEGS。

4. I/O 伺服器數量

update db cfg for dbname using NUM_IOSERVERS <磁碟數+2>;

七、資料重組：REORGCHK 與 REORG

1. REORGCHK 判斷是否需重組

# 更新統計並檢查所有表
db2 reorgchk update statistics on table all;

# 僅用現有統計檢查單表
db2 reorgchk current statistics on table vicky.staff;

關鍵指標：

表統計：CARD（列數）、OV（溢位列）、NP（含資料頁數）、FP（總頁數）、TSIZE（表大小）
索引統計：LEAF（葉頁數）、ELEAF（空葉頁）、NDEL（已刪 RID）、LVLS（層級）、ISIZE（平均鍵長）、KEYS（不重複鍵值數）

2. 離線重組

# 表重組（指定索引、臨時表空間、掃描模式）
db2 reorg table vicky.staff index ind1 use tempspace2 indexscan;

# 索引重組
db2 reorg indexes all for table vicky.staff allow read access cleanup only all;

3. 線上重組

# 允許寫入的線上重組
db2 reorg table vicky.staff index vicky.ind2 inplace allow write access;

# 暫停/恢復
db2 reorg table vicky.staff inplace pause;
db2 reorg table vicky.staff inplace resume;

# 查看歷史
db2 list history reorg all for dbname;

八、物化查詢表

MQT 預先計算並儲存查詢結果，極大加速彙總類查詢。

CREATE TABLE vicky.mqt1 AS
( SELECT name, location, salary
  FROM vicky.staff, vicky.org
  WHERE staff.dept = org.deptnumb AND salary > 20000
) DATA INITIALLY DEFERRED REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION;

# 手動刷新
REFRESH TABLE vicky.mqt1;

# 啟用自動路由（需統計信息）
RUNSTATS ON TABLE vicky.mqt1;
UPDATE DB CFG FOR sample USING DFT_REFRESH_AGE ANY;

九、並行處理配置

1. 啟用並行

# 實例級
UPDATE DBM CFG USING INTRA_PARALLEL YES;

# 資料庫級預設並行度
UPDATE DB CFG FOR EOS USING DFT_DEGREE ANY;

2. 並行度上限控制

# 應用級
SET RUNTIME DEGREE FOR (25) TO 4;
SET RUNTIME DEGREE FOR ALL TO 6;

# 實例級硬上限
UPDATE DBM CFG USING MAX_QUERYDEGREE 6;

十、多維叢集表

MDC 表按維度組織資料，實現區塊級索引，極適合數據倉庫星型模型。

CREATE TABLE vicky.sales
( YearAndMonth CHAR(4),
  Region CHAR(20),
  Product CHAR(2),
  Sales BIGINT
) ORGANIZE BY DIMENSIONS (YearAndMonth, Region);

# 複合維度
CREATE TABLE vicky.table1
( col1 CHAR(10), col2 CHAR(10), col3 CHAR(10),
  col4 INTEGER, col5 DECIMAL(10,2)
) ORGANIZE BY DIMENSIONS (col1, (col2, col3), col4);