全面解析IBM DB2 9中的查詢優化新特性

大多數主流關系數據庫管理系統，例如 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.PROF1

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 指南，將查詢優化推遲到運行時輸入變量已知的時候?？赡艿倪x項有 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 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

結果的基數

值以及值的計數

然后，這些信息被包括進來，用于幫助優化器在為那些符合條件的查詢（這些查詢不需要直接引用視圖）的選擇性估計和基數估計計算成本時做決定。 這將導致更精確的成本計算和更優的訪問計劃。

下面使用以上討論的相同的示例數據，闡釋在使用和不使用查詢的統計視圖的情況下基數估計的差別。

select * from T1,T2 where T1.C2='B' and T1.C1=T2.C1

1) 不使用統計視圖 - 在這種情況下，對于數據的基數的估計明顯不準確。根據估計，訪問計劃中的散列連接將返回 1 行，而實際上是 4 行。

清單2. 不使用統計視圖情況下的訪問計劃

Rows RETURN ( 1) Cost I/O | 1 HSJOIN ( 2) 15.1653 2/-----+----- 10 1 TBSCANTBSCAN ( 3)( 4) 7.58162 7.583011 1| | 10 10 TABLE:TABLE: KCHEN.T2KCHEN.T1

2) 使用統計視圖 - 在這種情況下，估計到的基數為 4 行，這相對于第 1 種情況有很大的提高，并且這次該估計是完全準確的。

注意，解釋輸出將包含以下診斷信息，以表明正在使用統計視圖。在這方面，db2exfmt 工具非常有助于確定是否正在使用統計視圖。

Diagnostic Details: EXP0147W. The following statistical statistical view may have been used by the optimizer to estimate cardinalities: 'KCHEN '.'V1'.

清單 3. 使用統計視圖情況下的訪問計劃

Rows RETURN ( 1) Cost I/O | 4 HSJOIN ( 2) 15.1653 2 /-----+----- 10 1 TBSCANTBSCAN ( 3)( 4) 7.58162 7.58301 1 1 | | 10 10 TABLE: TABLE: KCHEN.T2 KCHEN.T1

通過利用包含關于連接的附加信息（包括通過在 runstats 期間執行查詢而得到的 runstats 信息，但是沒有持久地物化詳細的實際結果集）的統計視圖，基數估計得到了改善。針對一個或多個查詢的統計視圖的使用是透明的，不必直接引用它。統計視圖有點類似于物化查詢表（Materialized Query Table，MQT），不同的是統計視圖不需要物化。實際上，DB2 中對統計視圖的支持與對 MQT 的支持具有類似的局限性。當前，統計視圖不支持 SUM、MAX 之類的聚合函數，也不支持 distinct 操作和 UNION、EXCEPT 或 INTERSECT 之類的集合操作。

在 DB2 V8 FP9 中，需要設置注冊表變量 DB2_STATVIEW，并且統計視圖中只能有 2 個表引用。此外，還需要手動收集統計信息，因為 runstats 不能工作于統計視圖。在 DB2 V9，所有這些限制都已經被去掉了。

通常，在涉及事實表和很多維表的星型連接場景中，統計視圖中將包括維表的列（本地謂詞列尤其重要），而事實表中的列則不需要包括進來。這是因為當事實表與維表連接時，事實表中列的數據分布不會改變，因此優化器只需根據事實表的列的分布統計，就可以得到準確的選擇估計。一個例外是在 V8 中，由于 MQT 路由限制，任何查詢謂詞引用的事實表列都必須包括在統計視圖中。

給定一個 3 表連接，可以創建多個統計視圖，即 T1 和 T2、T1 和 T3 以及 T1、T2、T3 上的統計視圖，這些視圖將包含生成最佳訪問計劃所需的所有統計信息，實際上前 2 個視圖就足夠了。

例子1:

Select * from T1, T2, T3 where T1.C1=T2.C1 and T1.C2=T3.C2 and T2.C1=2 and T3.C2 = 'B'Create view SCHEMA.V11 as select T2.* from T1, T2 where T1.C1 = T2.C1 Create view SCHEMA.V12 as select T3.* from T1, T3 where T1.C2 = T3.C2

其中，T1 是與維表 T2 和 T3 有 FK-PK 關系的事實表。

為改善估計，創建 V11 和 V12 這兩個統計視圖。

結束語

當查詢性能中出現例外情況時，優化指南和統計視圖特性對于彌補訪問計劃估計的不準確性非常有用。如果應用了所有標準的調優技術之后，仍然得不到期望的結果，那么可以考慮這兩個特性。但是使用這兩個特性時要謹慎，因為對于優化指南，需要額外的查詢匹配開銷，而對于統計視圖，又有編譯方面的開銷。此外，優化指南和統計視圖的特定內容也可能會隨著時間和數據庫狀態的改變而改變，例如，優化指南可能會隨著數據量而變化，統計視圖中的統計信息也有可能過時。所以，需要定期地檢查它們的實現，以便從它們的使用當中最大限度地獲益。