大多數主流關系數據庫管理系統，例如 IBM DB2、Oracle 和 Microsoft® SQL Server，都依賴於基於成本的優化器設計，來在數據庫服務器環境中的一組經常變化的條件（包括變化的查詢特征和變化的數據）的影響下，從很多可能的計劃中選擇一個最佳 SQL 執行計劃。具體而言，DB2 SQL 優化決定受系統配置（I/O 存儲特征、CPU 並行性和速度、緩沖池和排序堆設置、通信帶寬）、模式（索引、約束）、DB2 注冊表變量、DB2 優化級別和統計信息（關於表、列和索引的統計信息）的影響。這麼多復雜的因素，再加上數據本身的動態性，使得最佳計劃的評估對於任何數據庫系統而言通常都是一個復雜的過程。
考慮到生成最佳 SQL 執行計劃是一項不簡單的任務，DB2 對其已臻成熟的成本模型繼續進行了改進，並加入了新的功能，以提供更好的信息來幫助成本模型做出決定。統計視圖是一種強大的、新型的統計，它可以表示復雜謂詞或表之間的關系。REOPT 綁定選項將查詢優化推遲到 OPEN 時有可用輸入變量的時候。然後，優化器可以將輸入變量的值與編目統計進行比較，並為謂詞計算出一個更好的選擇估計。統計視圖和 REOPT 都使優化器可以計算出更精確的基數估計，而後選擇一個最佳查詢執行計劃。對於優化器不能選擇最佳查詢執行計劃的例外情況，DB2 已經增加了諸如 SELECTIVITY 子句和優化指南之類的特性。
在本文的討論中，我們來看看優化指南和統計視圖這兩個最新的增強。通過本文，您可以了解這些增強的作用是什麼，以及在某些情況下，在非數據分區（non-DPF）和數據分區（DPF）環境中，如何在應用程序內充分利用它們。
DB2優化概要文件和嵌入式指南
Version 8 FP9, DB2 for Linux, UNIX, and Windows 中包括優化概要文件功能，該功能將一個指南傳遞給優化器，用於指導優化器為 SQL 查詢生成所需的執行計劃，以覆蓋默認的成本模型。
很多人都曾在應用程序中碰到這樣的情況：大多數查詢工作負載都經過了適當的調優，並取得了較好的性能，但是，隨著用戶期望的增長，加上系統的復雜性和多樣性，仍然有少數 SQL 語句無法通過調優取得預期的性能。雖然人們已經盡了最大的努力力圖通過改變數據庫（例如使用索引建議器或者其他方法來改進索引、更新統計信息、改善數據群集及更改參數）來調優 SQL 語句，但是問題仍然存在。有時候，我們希望更直接地影響優化器，同時盡量避免更改應用程序。
這時候可以考慮使用優化指南。然而需要注意的是，先進的優化器在生成一個特定的訪問計劃時，必然有其原因，所以在應用指南之前，務必理解是什麼原因導致查詢的性能低下。優化指南使用起來並不難，但更具有挑戰性的任務是根據給定的數據庫環境判斷 SQL 語句的問題出在哪裡，並選擇適當的指南加以應用。
優化概要文件的工作原理
首先選擇一組您想要影響其訪問計劃的查詢。然後，將這些查詢和一些適當的指南放到一個 XML 優化概要文件中。為了通過驗證，這個優化概要文件必須遵從優化指南 XML 模式，並由一些區段組成，如清單 1 所示。
清單1.XML 優化概要文件
XML 優化概要文件以 OPTPROFILE 區段開始，該區段表明版本屬性。這個全局區段將規則全局地應用到所有 SQL 語句上。例如，可以指定使用哪個 REOPT 選項，使用哪個 MQT 表，或者使用什麼樣的查詢優化。statement profile 區段則表明將哪些特定的規則應用於 STMTKEY 元素中的 SQL 語句上。
如果有問題的 SQL 查詢不容易訪問到，那麼借助 XML 優化概要文件可以帶來很大的方便。例如，SQL 查詢可能處在一個應用程序中，而這個應用程序是不能更改的。在這種情況下，可以使用概要文件，在查詢文本成功匹配之後，通過觸發與查詢相關聯的指南來影響查詢行為。該環境中的所有 SQL 語句將嘗試從活動的優化概要文件中查找匹配項，而這種匹配是高效率、低開銷的。
如何啟用優化概要文
一個數據庫中可以有很多個優化概要文件，但是在實際情況中，更靈活的做法是創建一個主優化概要文件，將所有規則（statement profile）組織在一起，然後只需激活此概要文件，根據應用程序環境的不同，可以選擇以下幾種方法之一來激活概要文件。另外還需要將 DB2_OPTPROFILE 注冊表變量設置為 YES。
1.在CLP環境中：
使用 “SET CURRENT OPTIMIZATION PROFILE=KCHEN.PROF1” 語句在會話級將概要文件與所有 SQL 語句關聯，直到連接重置或者概要文件重置。這條語句還可以嵌入到應用程序中。
2.對於 CLI 應用程序或使用舊的 JDBC 驅動程序的JDBC應用程序
在db2cli.ini配置文件中設置 CURRENTOPTIMIZATIONPROFILE 關鍵字來關聯概要文件。對於 SAMPLE 數據庫，這個關鍵字是在 data source 區段中設置的。
[SAMPLE]
CURRENTOPTIMIZATIONPROFILE=KCHEN.PROF1
經過這樣設置後，應用程序執行中的 SQL 將嘗試與 KCHEN.PROF1 中的 SQL 語句進行匹配，以查找指定的規則，這些規則將覆蓋執行環境中常規的優化。
3.對於使用JCC Universal Driver的JDBC應用程序：
采用 JCC Universal Driver 的 JDBC 應用程序並不使用 DB2 CLI 層。雖然可以將一個系統包和綁定文件與動態 SQL 執行相關聯，但最好的做法是將 “SET CURRENT OPTIMIZATION PROFILE” 語句嵌入在 Java™ 應用程序中，在會話級關聯概要文件。
4.對於 SQL PL 過程
在創建 SQL PL 過程之前，使用 SET_ROUTINE_OPTS 過程調用將概要文件的名稱與 DB2 V8 FP13+ 或 DB2 V9 FP1+ 中特定的 SQL PL 相關聯。
CALL SYSPROC.SET_ROUTINE_OPTS('OPTPROFILE KCHEN.PROF1')
SQL PL 過程包含的 SQL 語句具有一些執行屬性，例如隔離級別或優化級別，這些屬性只能通過 DB2_SQLROUTINE_PREOPTS 注冊表變量來覆蓋。也可以用 SYSPROC.SET_ROUTINE_OPTS 過程覆蓋該選項。要激活一個概要文件，可以使用該存儲過程來關聯指南。
5.對於 C/C++ 應用程序中的嵌入式SQL：
對於嵌入式 C/C++ 應用程序，使用 OPTPROFILE 綁定選項。 嵌入式 SQC 程序需要使用 PREP 命令來編譯，該命令將創建綁定文件。這個綁定文件需要通過 OPTPROFILE 選項綁定到數據庫，例如：
bind prog1.bnd OPTPROFILE KCHEN.PROF
6.對於含嵌入式靜態 SQL 語句的 SQLJ 應用程序：
在定制階段使用 BINDOPTIONS 參數關聯概要文件。這個靜態 SQLJ 程序 prog1 被按如下所示進行翻譯和編譯：
sqlj prog1.sqlj
db2sqljcustomize -url jdbc:db2://SERVER:PORT/SAMPLE -user USER -password PASSWord
-bindoptions "OPTPROFILE KCHEN.PROF1" -storebindoptions prog1_SJProfile0
所有使用舊的 JDBC 驅動程序的 JDBC 程序，都將使用 db2cli.ini 中的設置。使用 Universal JDBC 驅動程序的 JDBC 程序屬於上述的第 3 類情況。需要注意的是，由於 SQLJ 為 SELECT SQL 語句生成一個隱式的 “DECLARE CURSOR” 子句，因此，為了使指南得到應用，優化概要文件除了包括 SELECT 語句外，還需要包括 “DECLARE CURSOR” 子句。
當應用程序執行時，將 SQL 與活動的概要文件中的指南相比較。如果存在一個匹配的 STMTKEY ，指南就會開始起作用；反之，假如指南被認為是不適用的或無效的，那麼就會返回一個 rc = 13 的 SQL0437W。DB2 Explain 工具對於幫助確定指南是否被選擇非常有用。Explain 的輸出會指明優化概要文件的名稱和有效的指南。概要文件中的指南通常覆蓋用於應用程序設置的常規優化，從而使概要文件能夠更好地控制計劃評估。
優化指南的例子
優化概要文件中的任何指南都必須遵從 DB2 提供的 XML 模式。如果沒有正確地指定指南，那麼指南將無效，並且在大多數情況下，將返回 rc = 13 的 SQL0437W。優化概要文件存儲在一個名為 SYSTOOLS.OPT_PROFILE 表中。如果從這個表中更新或刪除一個指南，那麼需要通過發出 FLUSH OPTIMIZATION PROFILE CACHE 語句更新緩存，使之可以被使用。需要注意的是，SQL 語句測試匹配是大小寫敏感的，但在嘗試匹配之前，DB2 將去除冗余空格和控制字符。
下面的例子演示了優化概要文件在 3 類情況下的使用，即常規優化、查詢重寫和計劃優化。
例子1: 總是使用索引 T1X （計劃優化）
假設在表 T1 的 (c2, c1) 列上有一個索引 T1X。根據優化器的成本計算，對於以下查詢，會導致一個表掃描。下面的代碼展示了如何強制使用一個索引。
例子2: 總是使用 REOPT（常規優化）
可以使用 REOPT 指南，將查詢優化推遲到運行時輸入變量已知的時候。可能的選項有 ONCE、ALWAYS 或 NONE。
例子3:只使用DB2 V9 中的Optimization Level 0（常規優化）
通常，對於一個應用程序而言，優化級別是固定的，但是如果要使一條特定的 SQL 語句在一個不同的優化級別上執行，那麼可以創建以下優化指南：
例子4:只使用 DB2 V9 中的Runtime degree ANY（常規優化）
可以有很多方法來修改內部分區的查詢的運行時等級。下面的代碼展示了優化指南如何為查詢指定運行時等級以及如何影響查詢的執行。
例子5: INLIST 改為嵌套循環連接（查詢重寫）
將值列表（inlist）改為使用 GENROW 函數非常有效，可以提高查詢的性能。在這個例子中，值列表被放在內存中的一個表中。
P.P_SIZE, P.P_TYPE, S.S_NATION
FROM KCHEN.PARTS P, KCHEN.SUPPLIERS S, KCHEN.PARTSUPP PS
WHERE P_PARTKEY = PS.PS_PARTKEY AND
S.S_SUPPKEY = PS.PS_SUPPKEY AND
P.P_TYPE IN ('BRASS', 'BRONZE') AND
P.P_SIZE IN (31, 31, 33, 34) AND
S.S_NATION = 'PERU']]>
例子6: 子查詢改為連接（查詢重寫）
在這個例子中，在查詢重寫期間，通過使用帶 ENABLE 屬性的 SUBQ2JOIN，將一個子查詢轉換成一個連接，以便更好地對其進行優化。
FROM KCHEN.PARTSUPP PS, KCHEN.LINEITEM
WHERE PS.PS_PARTKEY = L_PARTKEY AND
PS.PS_PARTKEY = ANY (
SELECT P_PARTKEY FROM KCHEN.PARTS
WHERE P_BRAND <> 'Brand#45' AND
P_NAME = 'peach snow puff bisque misty' AND
P_TYPE <> 'TIN')
GROUP BY PS_PARTKEY]]>
例子7: 影響連接順序 3、4、1、2 （計劃優化）
通常，查詢的連接順序很大程度上決定了查詢的執行性能，因為越早地過濾行，效率越高。可以使用以下指南來影響連接順序。注意，當出現多個表引用時，使用 TABLEID 屬性，而不是 TABID 屬性。
where t71.c1 = t72.c1 and
t72.c2 = t74.c2 and
t74.c1 = t73.c1 and
t73.c2 = t71.c2 and
t71.c3 = t74.c3 and
t72.c3 = t73.c3]]>
例子8: 客戶使用情況（計劃優化）
在批處理運行過程中，當刷新一個 MQT 時，客戶會遇到性能問題。當為 MQT 定義中涉及的表 tab2 填充數據時，就會觸發對 MQT 的刷新。下面的例子代碼可以演示這個問題。
create table tab1 (i int, b char(30))
create table tab2 (i int, b char(150))
create table mqt1 (cnt,val) as
(select count(*), tab2.b from tab2, tab1 where tab1.b=tab2.b group by tab2.b)
data initially deferred refresh immediate
create index i11 on tab1 (i asc, b asc
create index i12 on tab1 (b asc, i asc)
create index i21 on tab2 (i asc, b asc
create index i22 on tab2 (b asc, i asc
insert into tab2 values(14,substr(char(current timestamp),1,5)
在這個場景中，經過分析，可以確定使用索引 I11 來訪問表 TAB1 是最優的，但是優化器的默認行為不會這麼做，即使在調優之後也仍然不會這樣做。但是，可以通過創建下面的指南來影響優化器，使之考慮 I11 索引，從而將 MQT mqt1 的刷新速度提高兩倍以上。
統計視
基本上，關系數據庫中的數據會因事務和批量更新而發生變化 —— 即使是數據集市或數據倉庫中的內容也會隨著時間而變化。SQL 工作負載常常是動態的 SQL（而不是靜態的），所以任何基於成本的優化器通常都必須對數據、數據選擇性和數據基數做出假設，但是很多情況下，數據的分布呈難以預測的不均勻性，數據域值本身的特性以及表和視圖的相互依賴關系會使優化器很易出錯。
由於查詢是動態的，在編譯時並不知道其選擇標准，因此，即使有了關於數據的完整的分布統計，仍然可能生成錯誤的計劃。如果優化器能預知查詢結果（或部分查詢結果），那麼該信息對於幫助確定更精確的訪問計劃將非常有用。
基本上，可以有以下兩點假設：
◆均勻分布
◆域值
為了理解統計視圖，我們首先看看以上兩點假設，通常情況下這兩點假設可能是錯誤的。因此，在進行查詢計劃優化時，就需要使用統計視圖。
均勻分
考慮以下數據
C1
1
2
3
3
3
3
7
7
9
10
runstats（無分布）將提供關於 C1 的以下信息
CARD = 10,
COLCARD = 6,
LOW2KEY = 2,
HIGH2KEY = 9
那麼：
◆C1=3 的行的數量將被估計為 10/6 = 1.67。
◆C1=4 和 C1=8 之間的值域被估計為 ((8-4)/(9-2)) * 10 = 5.71。
但是，如果將數據變化一下，以反映數據不均勻、大跨度的分布，如下所示
C1
1
2
3
3
3
3
7
7
99
100
那麼：
C1=3 的行的數量被估計為 10/6 = 1.67。
C1=4 與 C1=8 之間的值域被估計為 ((8-4)/(99-2)) * 10 = 0.41
如果數據是完全均勻分布的，如下所示
C1
1
2
3
4
5
6
7
8
9
在本文的討論中，我們來看看優化指南和統計視圖這兩個最新的增強。
通過本文，您可以了解這些增強的作用是什麼，以及在某些情況下，在
非數據分區（non-DPF）和數據分區（DPF）環境中，如何在應用程序
內充分利用它們。
10
那麼：
C1=3 的行的數量將被估計為 10/10 = 1。
C1=4 與 C1=8 之間的值域被估計為 ((8-4)/(9-2)) * 10 = 5.71。
C1=3 與 C1=7 之間的值域被估計為 ((7-3)/(9-2)) * 10 = 5.71。
所以，當數據均勻分布時，無論值和范圍如何，真實的結果與估計的結果都更加一致。
即使擁有頻率值和分位數值之類的分布統計信息（這些信息可以大大減少等於和范圍謂詞的估計錯誤），也仍然會出現估計錯誤無法接受的情況。
域值
a) 現在看看包含以下數據的兩個表的連接 T1.C1 = T2.C1，其中一組數據包含另一組數據：
T1.C1
1
2
3
4
5
6
7
8
9
10
T2.C1
1
2
3
4
5
6
7
8
9
10
謂詞的選擇性定義如下：
Selectivity = 1 / ( max ( C1 colcard , C2 colcard ) ) = 0.1
基數為 10 * 10 * 0.1 = 10。
b) 如果表連接 T1.C1 = T2.C1 中的數據在兩組數據相交處稍微有所不同，一個表中的數據沒有包含另一個表中的數據：
T1.C1
1
2
3
4
5
6
7
8
9
10
T2.C1
1
2
2
2
2
5
12
13
14
15
在這種情況下，T1.C1 的值，例如 7，不能與 T2.C1 連接，而 T2.C1 的值，例如 12，也不能與 T1.C1 連接，但是估計算法並不知道這一點，因而會做出不准確的假設，認為 T1 中的一個值很可能與 T2 中的任意值連接，反之亦然。
基數仍然是 10 * 10 * 0.1 = 10。
所以成本是一樣的，但是 a) 的實際行輸出結果為 10，b) 的實際行輸出結果為 6。
結果
1
2
2
2
2
5
顯然，這裡存在不一致性，而且，對於更復雜的連接，這種錯誤估計的問題很可能變得更糟糕。而 V8 FP9 以上版本提供的 DB2 統計視圖特性，正是為彌補這一類由於數據分布和值導致的不一致性而設計的。
為了理解統計視圖的作用，我們來考慮一個更實際一點的連接場景：
T1.C1 T1.C2
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
T2.C1
1
2
2
2
2
5
5
13
14
15
在這種情況下，連接謂詞 T1.C1=T2.C1 和 T1.C2='A'（或 C2 的任何值）返回的基數估計將為 1。但是，如果本地謂詞為 T1.C2='B' 或 T1.C2='E'，那麼這個估計就錯得太厲害了。請看上面兩個表在 T1.C1 = T2.C1 上的連接所產生的如下結果。
T1.C1 T1.C2 T2.C1
1 A 1
2 B 2
2 B 2
2 B 2
2 B 2
5 E 5
5 E 5
為了彌補這種估計錯誤，可以創建和准備一個統計視圖，並像下面這樣加以利用：
Create vIEw SCHEMA.V1 as select * from T1, T2 where T1.C1 = T2.C1
Alter vIEw SCHEMA.V1 enable query optimization
Runstats on table SCHEMA.V1 with distribution
對於統計視圖，ENABLE QUERY OPTIMIZATION 子句將導致該視圖以及與之相關聯的統計信息被用於改進查詢優化。這裡只需收集包含分布特征的數據 runstats 信息。runstats 信息是統計視圖部署的關鍵，必須提供比基本表更完整的信息。有時候，列組或類似的統計選項會很有用。
現在，統計視圖將包含在整個結果集上收集到的關於連接之後的數據分布的統計信息，無論是在 non-DPF 還是 DPF 環境中，這個信息都是完整的，沒有推斷成分。有時候，runstats 可能要花更多的時間，這可能是由於視圖本身的規劃沒做好。在運行 runstats 之後，以下附加信息會成為已知的信息：
結果的列的 COLCARD
結果的基數
值以及值的計數
然後，這些信息被包括進來，用於幫助優化器在為那些符合條件的查詢（這些查詢不需要直接引用視圖）的選擇性估計和基數估計計算成本時做決定。 這將導致更精確的成本計算和更優的訪問計劃。
下面使用以上討論的相同的示例數據，闡釋在使用和不使用查詢的統計視圖的情況下基數估計的差別。
