提升商業智能環境中 DB2 查詢效能的六大策略 (上)

引言

決策支援系統 (DSS) 中的大型查詢通常為大量資料掃描的純 SELECT 語句。本系列將逐步介紹六種優化技術，並以同一 SQL 語句為基準，使用 db2batch 實測效能。示範環境：Windows 平台 DB2 9，啟用 Database Partitioning Feature (DPF) 模擬多分區。

星型結構與測試環境

星型結構：事實表 SALES_FACT (2006 年銷售明細) + 三個維度表 DATE_DIM、PRODUCT_DIM、STORE_DIM。

分區組：FACT_GROUP (分區 0-3) 存放事實表；DATE_GROUP (分區 1)、PRODUCT_GROUP (分區 2)、STORE_GROUP (分區 3) 各存一個維度表。

表空間：各表獨立 SMS 表空間，共用緩衝池 IBMDEFAULTBP (1000 × 4K 頁)。

基準查詢

比較 1 月與 11 月 10 家門市銷售額，使用 CTE (TMP1, TMP2) 並 LEFT OUTER JOIN。完整 SQL 請見清單 2。

環境配置

• DB2 9 Enterprise Edition (32-bit), Fix Pack 0
• Windows XP SP2, 雙核心, 2.5 GB 實體記憶體
• 循環日誌：LOGFILSIZ=8192, LOGPRIMARY=100, LOGSECOND=150

資料庫建立與配置步驟

1. 建立分區：db2ncrt /n:1 /u:user,pwd /i:DB2 /m:Machine /p:1 (分區 1-3)
2. 建立資料庫：db2 create database dss_db on D:
3. 配置參數：使用 db2_all 同步設定 DBM/DATABASE CFG (CPU speed, intra_parallel, locklist, dft_queryopt 等)
4. 建立分區組與表空間：執行 STORAGE.ddl (清單 11)，指定 NO FILE SYSTEM CACHING 避免快取干擾
5. 調整緩衝池：ALTER BUFFERPOOL IBMDEFAULTBP SIZE 1000

使用 db2batch 進行效能基準測試

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r results.txt

關鍵選項：-iso CS 符合應用預設隔離級別，-o p 5 取得詳細快照，-i complete 輸出編譯/執行/擷取時間。

建立基準表格與載入測試資料

1. 執行 TEST1.ddl (清單 13) 建立四張表與主鍵
2. 載入維度表：
   db2 -td@ -vf date_insert.txt (365 筆)
   db2 -td@ -vf product_insert.txt (60,000 筆)
   db2 -td@ -vf store_insert.txt (201 筆)
3. 載入事實表：約 1.5 小時，單一交易產生大量日誌；替代方案使用預存程序分批 COMMIT 減少日誌壓力 (清單 22)
4. 建立 EXPLAIN 表：db2 -tvf EXPLAIN.DDL 用於後續存取計畫分析

下一步預告

第二部分將依序實作六大優化方法：參考完整性約束、重複 MQT、MDC、表分區、表分區+MDC、預先彙總 MQT，並呈現完整效能對比數據。