使用DB2look實用程序重新創建優化器訪問計劃(1)

簡介

在作為 DB2 UDB 支持分析員（Support Analyst）工作時，我經常從處理優化器或查詢計劃問題的客戶那裡聽到下列問題：

我如何在接手生產環境的測試環境中重新創建相同的查詢訪問計劃呢？

我們常常需要將生產環境復制到測試環境中，包括為查詢分析目的重新創建相同的訪問計劃。

例如，在生產中，您可能會遇到使用糟糕訪問計劃的查詢所導致的性能問題，並且需要在測試系統上復制該訪問計劃以嘗試一些不同的策略，例如操作統計數據，修改優化級別，對 DB2 注冊表變量嘗試不同的設置等等，以便提高性能。

在理想的世界中，您需要讓測試環境盡可能接近地匹配生產。也就是說，您需要在兩個環境中使用完全相同的硬件、操作系統維護級別和配置、DB2 級別和配置，以及在測試中使用與生產中相同的數據。然而，並非總是可以達到這種理想情況。如果生產環境具有極其大量的數據，您或許就沒有容量來保存生產系統的測試副本。

db2look 實用程序可以用於達到該目標，即使您無法復制所有的生產細節。

本文將解釋如何可以在測試系統上模擬生產系統，而無需真正的數據來重新創建查詢計劃問題。該功能將幫助您調試查詢和理解訪問計劃問題，且不打斷生產環境中的工作。但是請注意，如果需要測試結果訪問計劃的執行，則仍然需要將盡可能多的數據從生產環境裝入測試環境。測試系統和生產系統之間的差別仍然總是可能足以導致測試上的執行特性不匹配生產上的。這部分的分析（性能調優）既是一門科學，又是一門藝術。

優化器或查詢編譯器領域中的其他問題，例如 SQL0901N 錯誤或實例崩潰，也可以使用本文中所解釋的方法來重新創建。您可以嘗試各種策略，如測試最新的補丁包（如果系統是處於更老的補丁級別），嘗試不同的優化級別、不同的注冊表變量等等，以便查看這些修改是否將解決問題。

讓我們看一看 db2look 中用於達到該目標的選項。

db2look 命令及其選項

下面是用於從生產系統捕獲所需信息的命令：

清單 1. 重新創建優化器問題的命令

現在，讓我們更詳細地看一看這些 db2look 命令選項。

生成緩沖池、表空間和數據庫分區組信息。

db2look -d -l -o storage.out

下面是對以上 db2look 命令中所用選項的描述：

-d：數據庫名 —— 該選項必須指定。

-l：生成數據庫布局。這是用於數據庫分區組、緩沖池和表空間的布局。

-o：將輸出重新定向到給定的文件名。如果未指定 -o 選項，然麼輸出將為標准輸出（stdout），通常是輸出到屏幕。

-l 選項對於模擬生產環境十分重要。理想情況下，您需要具有相同的緩沖池、數據庫分區組（如果處於多分區環境中）和表空間信息（包括臨時表空間）。但是，如果您受到了內存約束，無法分配生產中所具有的大型緩沖池，那麼就使用 db2fopt 命令。我稍後將在本小節中更詳細地討論該命令。

並非總是可以在測試中設置與生產中相同的表空間。例如，可能設置了大型設備，卻無法靈活地在測試中創建相同的設備大小。或者，可能根本無法在測試環境中獲得單獨的表空間設備。此外，或許無法在測試中設置與生產中相同的路徑。需要適當地更改路徑、設備和文件以適應測試環境。

下面是優化器為表空間所使用的重要信息。這就是您需要確保在測試和生產中相同的信息。（注意：這裡所展示的數字是一個例子。您應在測試中使用與您生產中相同的設置。）

PREFETCHSIZE 16　
EXTENTSIZE 16　
OVERHEAD 12.670000　
TRANSFERRATE 0.180000

如果生產中表空間是“由數據庫管理的”，那麼在測試中也應該是“由數據庫管理的”。如果它在生產中是“由系統管理的”，那在測試中也應該是這樣的方式。

注意：如果這是具有多個物理分區（MPP）的系統，那麼測試中數據庫分區組中的分區數目就必須相同。然而，物理機器的數目不必相同。測試和生產中整個 MPP 環境中邏輯分區的數目必須相同。

生成配置參數和注冊表變量

db2look -d <dbname> -f -fd -o config.out

這裡，我將使用下列參數：

-f：提取配置參數和注冊表變量。如果指定了該選項，就會忽略 -wrapper 和 -server 選項。

-fd：為 opt_buffpage 和 opt_sortheap 生成 db2fopt 語句，以及其他配置和注冊表設置。

該命令的輸出如下所示：

清單 2. db2look 命令的示例輸出：

$ db2look -d sample -f -fd　
-- No userid was specified, db2look trIEs to use
Environment variable USER　
-- USER is: SKAPOOR　
-- This CLP file was created using DB2LOOK Version 8.2　
-- Timestamp: Sat Mar 26 00:13:36 EST 2005　
-- Database Name: SAMPLE　
-- Database Manager Version: DB2/6000 Version 8.2.2　
-- Database Codepage: 819　
-- Database Collating Sequence is: UNIQUE　
CONNECT TO SAMPLE;　
--------------------------------------------------------　
-- Database and Database Manager configuration parameters　
--------------------------------------------------------　
UPDATE DBM CFG USING cpuspeed 6.523521e-07;　
UPDATE DBM CFG USING intra_parallel NO;　
UPDATE DBM CFG USING federated NO;　
UPDATE DBM CFG USING fed_noauth NO;　
!db2fopt SAMPLE update opt_buffpage 50000;　
!db2fopt SAMPLE update opt_sortheap 10000;　
UPDATE DB CFG FOR SAMPLE USING locklist 1000;　
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;　
UPDATE DB CFG FOR SAMPLE USING maxlocks 10;　
UPDATE DB CFG FOR SAMPLE USING avg_appls 1;　
UPDATE DB CFG FOR SAMPLE USING stmtheap 2048;　
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;　
---------------------------------　
-- Environment Variables settings　
---------------------------------　
!db2set DB2_ANTIJOIN=yes;　
!db2set DB2_INLIST_TO_NLJN=yes;　
COMMIT WORK;　
CONNECT RESET;　
TERMINATE;

-f 和 -fd 選項是用於提取配置參數和注冊表變量的關鍵選項，而優化器將在訪問計劃階段使用這些配置參數和環境。在上面的 清單 2 中，請注意下列 -fd 選項所產生的輸出：

!db2fopt SAMPLE update opt_buffpage 50000;　
!db2fopt SAMPLE update opt_sortheap 10000;

db2fopt 命令告訴優化器為“緩沖池大小（Buffer pool size）”使用指定的值，而非將可用緩沖池變量的頁面加起來。（db2exfmt 輸出中的緩沖池大小將在下面的 緩沖池大小 一節中進行進一步的討論。）例如，假設由於測試系統上的內存約束，您無法獲得大型的緩沖池，並且希望將大小配置得相同，實際上卻不是真正有這麼大。使用將生成必要的 db2fopt 命令的 -fd 選項來告訴優化器使用指定大小，而非基於對該數據庫可用的緩沖池進行計算。