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

DB2 UDB v8.1 數據庫調優最佳實踐（二）：關鍵配置參數詳解

編輯：DB2 教學

核心內存與排序配置

SORTHEAP（排序堆大小）

SORTHEAP 定義私有排序或共享排序可使用的最大內存頁數。每個排序操作都會獲得獨立的排序堆，由數據庫管理器按需分配。當排序所需內存超過 SORTHEAP 時會發生排序溢出，但更隱蔽的原因是統計信息過時或數據分布傾斜導致優化器低估排序需求，從而請求過小的堆。因此，保持統計信息最新至關重要，同時需確保排序非因缺失索引而產生。

OLTP 環境：建議初始值 128 頁。
OLAP 環境：建議 4096–8192 頁。
若出現兩位數的 "Sort overflows"，需增加 SORTHEAP。
"Number of hash join overflows" 非零時，按 256 頁遞增直到歸零。
"Number of small hash join overflows" 非零時，按 10% 遞增直到歸零。

CHNGPGS_THRESH（緩衝池修改頁面閾值）

指定緩衝池中被修改頁面佔比達到該百分比時，觸發異步頁面清除器將髒頁寫入磁盤。唯讀環境不啟用清除器。

OLTP：20–40 之間（高更新量用 20），降低該值可使 I/O 清除更積極但單次工作量較小。
若無大量 INSERT/UPDATE，OLTP/OLAP 均可用默認值 60。
若 "Dirty page steal cleaner triggers" 為兩位數，嘗試降低此參數。
若 "Buffer pool data writes" 高而 "Asynchronous pool data page writes" 低，亦嘗試降低。
FixPak 4 引入新頁面清除算法，設置 DB2_USER_ALTERNATE_PAGE_CLEANING=YES 可忽略 CHNGPGS_THRESH，但需確保 NUM_IOSERVERS ≥ 3。

NUM_IOCLEANERS（異步頁面清除器數量）

指定將修改頁面從緩衝池寫入磁盤的異步清除器數量。初始值設為 CPU 數。清除器同時啟動，過多會影響性能。

異步寫入百分比 (AWP) ≥ 90% 時減少清除器；AWP < 90% 時增加。
AWP = (("Asynchronous pool data page writes" + "Asynchronous pool index page writes") * 100) / ("Buffer pool data writes" + "Buffer pool index writes")

NUM_IOSERVERS（I/O 服務器數量）

用於執行預取操作。初始值設為數據庫跨越的物理磁盤數 + 1 或 2，但不超過 CPU 數的 4–6 倍。若 "Time waited for prefetch (ms)" 明顯，可嘗試增加。

MAXFILOP（每代理最大打開文件數）

超過此值會強制關閉文件，頻繁開關降低性能。SMS 表空間通常佔用更多文件。增加該值直到 "Database files closed" 為 0。

日誌配置

LOGPRIMARY、LOGSECOND、LOGFILSZ

主日誌文件預分配，次日誌按需分配，LOGFILSZ 定義單個文件大小。若 "Secondary logs allocated currently" 較大，需增加 LOGFILSZ 或 LOGPRIMARY（總和不超 256）。可參考 "Maximum total log space used"。較大日誌文件利於性能但可能增加災難恢復時的事務丟失風險。

LOGBUFSZ（日誌緩衝區大小）

提交事務或緩衝區滿時寫盤。OLTP 建議 ≥256 頁，OLAP 建議 128 頁。若頻繁見 "Log pages read" 超過一對，需增加。若增加時報錯，同量增加 DBHEAP 後重試。

緩存與優化參數

PKGCACHESZ（包緩存大小）

緩存靜態/動態 SQL，減少目錄訪問或重編譯。應大於 "Package cache high water mark"。若 "Package cache overflows" 非零，增加此值。包緩存命中率 (PCHR) 應盡可能接近 100%：
PCHR = (1 - "Package cache inserts" / "Package cache lookups") * 100

CATALOGCACHE_SZ（目錄緩存大小）

緩存系統目錄信息，DPF 環境尤為重要。不斷增加直到 OLTP 的目錄緩存命中率 (CCHR) ≥ 95%：
CCHR = (1 - "Catalog cache inserts" / "Catalog cache lookups") * 100
若 "Catalog cache overflows" > 0 或高水位等於最大值，需增加。

進階調優參數（需實測驗證）

INTRA_PARALLEL（分區內並行）

默認 NO 適合高併發 OLTP；YES 適合低併發、複雜 SQL 的 OLAP/DSS。混合負載通常受益於 NO。啟用時從共享內存分配排序內存，高併發可能帶來額外開銷。非 OLTP 系統若 CPU:分區 ≈ 4:1 且平均 CPU 負載 50%，開啟可能提升性能。

DFT_QUERYOPT（默認查詢優化級別）

混合 OLTP/OLAP：5 或 3
純 OLTP：較低級別
純 OLAP：較高級別
簡單 SELECT/短查詢（<1秒）：1 或 0
多表多連接謂詞：1 或 2
長查詢（>30秒）或 UNION ALL VIEW：7
多數環境避免用 9

UTIL_HEAP_SZ（實用程序堆大小）

BACKUP/RESTORE/LOAD 共用。若使用 LOAD，每 CPU 至少 10000 頁。

NEWLOGPATH（日誌路徑）

將日誌移至獨立高速本地磁盤可顯著提升性能。

SQL 分析與工具

Design Advisor (db2advis)

輸入問題查詢或工作負載，推薦索引。可從快照捕獲動態 SQL、語句事件監視器收集 SQL、或從 SYSCAT.STATEMENTS 提取靜態 SQL。支持 Control Center GUI 和 CLP 命令行。示例：
db2advis -d sample -s "select count(*) from sales where region = 'Quebec'" -t 1
多語句用文件指定頻率：
--#SET FREQUENCY 10\nSELECT * FROM SALES;\n--#SET FREQUENCY 2\nSELECT FIRSTNME FROM EMPLOYEE WHERE EMPNO = ?;

語句事件監視器

創建監視器記錄 STATEMENTS 事件，輸出至文件。提供 UNIX (ksh) 和 Windows (bat) 腳本示例。輸出包含動態 SQL 文本，靜態 SQL 需結合包名、節號用 db2expln 或查詢 syscat.statements 獲取。關鍵搜尋字串：
" sqlcode: -"（錯誤）、" Rows read: "（大量讀取需索引或更新統計）、" Exec Time: "（耗時語句）、" Sort overflows:"（排序溢出）、" Fetch Count:"（fetch 次數）。

解釋工具

Visual Explain：GUI 圖形化展示訪問計劃，雙擊節點查看詳情。從 Control Center 右鍵 "Explain SQL" 進入。
文本 Explain (db2exfmt/db2expln)：適合共享輸出。
explain all for SQL_statement\ndb2exfmt -d dbname -g tic -e explaintableschema -n % -s % -w -1 -# 0 -o outfile
多語句用文件：
db2 -tf file_with_statements\ndb2exfmt ... -w % ...
靜態 SQL 包用 db2expln -database dbname -schema schema_name -package package -output outfile.txt

分析重點：ORDER BY/GROUP BY/DISTINCT 可受益於索引/MQT 消除排序；昂貴操作（大排序、溢出、表掃描）需更多排序空間、更好索引、更新統計或改寫 SQL；表掃描、全索引掃描、無選擇性索引掃描均可優化。

SQL 語句調優技巧

SELECT ... FOR UPDATE：預先加 U 鎖減少後續更新鎖升級開銷。
SELECT ... FOR READ ONLY：允許塊操作提升 FETCH 性能。
OPTIMIZE FOR n ROWS：優化前 n 行返回時間，影響塊大小。
FETCH FIRST n ROWS ONLY：限制結果集大小。
大量 DELETE 用 ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE 或 IMPORT REPLACE 空文件。
頻繁 COMMIT 釋放鎖，減少死鎖/等待。
重複語句使用參數標記避免重複編譯；OLAP 環境注意數據傾斜導致計劃不優。
語句級指定隔離級別（UR/CS/RS/RR）提升並發粒度。

偏態數據下的 SELECTIVITY 指定

啟用 DB2_SELECTIVITY=YES 並重啟實例，配合優化級別 5 使用。可對基本謂詞、LIKE 謂詞指定選擇性 (0~1)。
SELECT c1,c2,c3 FROM T1,T2,T3 WHERE T1.x=T2.x AND T2.y=T3.y AND T1.x >= ? selectivity 0.000001 AND T2.y < ? selectivity 0.5 AND T3 = ? selectivity 1
視為最後手段。

持續維護

REORG 與 REORGCHK

REORGCHK UPDATE STATISTICS ON TABLE ALL：收集統計並識別需重組對象。
REORGCHK CURRENT STATISTICS ON TABLE ALL：基於現有統計識別。
輸出 F1–F8 列有 * 表示需 REORG。
REORG TABLE schema.tablename：回收空間、消除溢出行。
REORG INDEXES ALL FOR TABLE schema.tablename：重組所有索引。
REORG TABLE schema.tablename INDEX schema.indexname：按指定索引/聚簇索引物理排序。

RUNSTATS

REORG 後必須重新收集統計。大表可用抽樣或後台運行。查詢未做過 RUNSTATS 的對象：
SELECT char(tabname,40) FROM syscat.tables WHERE type='T' AND stats_time IS NULL
常用命令：
RUNSTATS ON TABLE schema.table AND INDEXES ALL
RUNSTATS ON TABLE schema.table AND SAMPLED DETAILED INDEXES ALL（大索引）
RUNSTATS ON TABLE schema.table WITH DISTRIBUTION（非均勻數據）
可生成腳本批量執行。

REBIND

REORG/RUNSTATS 後需 REBIND 靜態包：
db2rbind dbname -l logfile.out ALL

DPF（數據庫分區功能）性能

何時分區

64 位系統首選大型 SMP 避免分區複雜性。分區可加速 SELECT/LOAD/BACKUP/RESTORE，但小數據庫開銷可能抵消收益。亦可突破單分區限制（如 4K 頁面 64GB 表大小上限）。

分區數量

難以一概而論。起步約每分區 4 CPU。若 CPU 利用率長期 <40%，考慮增加分區。每機器分區越少越利於本地旁路和並置。

選擇分區鍵

顯式指定，勿用默認。
必為主鍵/唯一索引子集。
高基數列優先；低基數列易導致數據傾斜。
列數不宜過多（≤4），否則哈希計算開銷大。
用常連接列作為分區鍵促進並置連接。
整數 > 字符 > 小數效率遞減。

表並置

將連接表放入同分區組表空間，分區鍵列數、類型一致，實現本地連接避免數據移動。中小唯讀表無法同鍵分區時，可用復制 MQT：
CREATE TABLE replicated_table AS (SELECT * FROM source_table) DATA INITIALLY DEFERRED REFRESH IMMEDIATE REPLICATED

AIX 上啟用 DB2_FORCE_FCM_BP=YES 可通過共享內存加速分區間數據傳輸。

重新平衡數據

REDISTRIBUTE DATABASE PARTITION GROUP 重新平衡並更新哈希映射。用 HASHEDVALUE、DBPARTITIONNUM 檢查分布：
SELECT DBPARTITIONNUM(column), COUNT(*) FROM table GROUP BY DBPARTITIONNUM(column)

系統資源瓶頸排查

CPU 利用率

總利用率（用戶+系統）>80% 視為 CPU 瓶頸。長期平均關注，忽略短時 100%。Unix 用 vmstat 3，Windows 用 Perfmon/Task Manager。高 CPU 常因大表掃描，分析快照中 "rows read" 高的 SQL 尋找索引機會。進程級用 ps uax (Unix) 或 Perfmon (Windows) 定位高佔用進程。