簡介
DB2 優化器是一款高度復雜的基於成本的優化器。然而,優化決策是各種屬性的復雜函數,這些屬性包括數據庫環境、查詢特征以及數據本身。在這些不同的獨立因素的相互作用下,優化器可能會選擇不太優化的執行計劃。由於不可能總是修改數據庫設置或數據的內在特征,因此需要使用一種工具來支持用戶影響計劃的選擇,從而適合他們獨特的設置特征。優化准則是一種強大的機制,使用戶能夠影響關鍵的執行計劃屬性,包括表訪問方法、索引選擇、連接方法和連接順序,從而解決可能出現的任何性能問題。最新的 DB2 V9.7 for Linux, UNIX, and Windows (DB2 9.7) 發行版對優化准則基礎設施進行了增強,引入了特定於 XML 的准則並擴展了現有准則的范圍以包含 XML 操作符。
本文旨在向 DB2 pureXML 用戶介紹優化准則基礎設施,並介紹針對 SQL/XML 和 XQuery 工作負載設置、使用和維護優化准則的所有步驟。同時提供了多種故障排除技巧和資源,使用戶獲得成功實現准則所需的所有工具和信息。
本文使用的數據庫設置
Transaction Processing over XML (TPoX) 是一個基於金融應用程序場景(特別是有價證券交易)的應用程序級別的 XML 數據庫基准。它使用一個真實的 XML 模式建模與有價證券交易有關的數據。它非常靈活,提供了各種 knob 和調優參數,可以修改數據分布、工作負載構成等等。本文使用一個經過輕微修改的 TPoX 基准來展示 XML 准則基礎設施中可以用來影響優化器計劃的各種選項。為了簡單起見,本文的樣例查詢忽略了 TPoX XML 數據中的名稱空間。
本文使用修改後的 TPoX 表,這些表使用清單 1 所示的 DDL 創建:
清單 1. 用於創建 TPoX 表的 DDL
create table security (security_id int, security_type varchar(10), SDOC XML);
create table custacc (CDOC XML);
create table order (security_id int, ODOC XML);
這些表上創建了以下關系索引:
create index sec_type on security?security_type);
這些表上創建了以下 XML 索引:
清單 2. 表上創建的 XML 索引
create index sec_industry on security(sdoc) generate key using
XMLpattern '/Security/SecurityInformation/StockInformation/Industry' as SQL varchar(30);
create index sec_symbol on security(sdoc) generate key using
XMLpattern '/Security/Symbol' as SQL varchar(30);
create index sec_name on security(sdoc) generate key using
XMLpattern '/Security/Name' as SQL varchar(100);
create index acc_currency on security(sdoc) generate key using
XMLpattern '/Customer/Accounts/Account/Currency' as SQL varchar(3);
概要文件基礎設施概述
優化概要文件是指包含優化准則的 XML 文檔。優化准則分為兩個類別:
全局准則:指定優化概要文件生效後需要對所有語句考慮的訪問計劃參數。
語句級別准則:指定只對特定語句應用的計劃屬性。
每個語句級別准則包含:
一個語句鍵:識別其訪問計劃必須受到影響的語句。
一個操作:指定理想的訪問計劃。
“XML 准則的使用場景” 一節包含了有關優化概要文件剖析的詳細信息。
優化概要文件允許您在無需更改應用程序或數據庫配置的情況下影響訪問計劃。應用程序和概要文件的分離實現了兩者的獨立開發。用戶只需編寫 XML 概要文件文檔,將其插入到數據庫,然後指示優化器使用這些概要文件。一旦概要文件被啟用,優化器將自動將優化准則匹配到對應的語句。概要文件中指定的准則不需要包含計劃中的所有操作符的詳盡說明。可以定義那些只影響執行計劃的某些屬性的准則,比如查詢中的某個表的索引說明,或者一對表的連接順序。優化器根據指定的准則實現概要文件中指定的部分計劃,並通過在多個計劃之間做出基於成本的決策來智能地完成計劃的剩余部分。
設置和使用優化概要文件詳解
必須通過以下步驟來使用優化概要文件:
創建 XML 概要文件。
從優化准則中的查詢引用表。
創建 OPT_PROFILE 表。
在 OPT_PROFILE 表中加載或插入概要文件。
啟用一個概要文件。
發出查詢。
檢驗是否已經應用了概要文件以及是否實現了理想的訪問計劃。
以下小節將詳細介紹每一個步驟。
創建 XML 概要文件
優化概要文件被編寫為 XML 文件,該文件必須遵循 DB2 LUW Information Center 針對每個版本發布的 “Current Optimization Profile Schema (COPS)” 。優化概要文件包含其訪問計劃需要受到影響的所有 SQL 查詢。
清單 3. 樣例優化概要文件
<?XML version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<!--
Global optimization guidelines section.
Optional but at most one.
-->
<OPTGUIDELINES>
<REOPT VALUE="ALWAYS"/>
</OPTGUIDELINES>
<!--
Statement profile section.
Zero or more.
-->
<STMTPROFILE ID="Sample Optimization Profile">
<STMTKEY SCHEMA="TPOX">
<![CDATA[SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/StockInformation
[Industry = "Software"]') AND
XMLEXISTS('$SDOC/Security/Symbol[.<>"IBM"]')]]>
</STMTKEY>
<OPTGUIDELINES>
<XANDOR TABLE="SECURITY"/>
</OPTGUIDELINES>
</STMTPROFILE>
每個 XML 概要文件的開始部分是一個 OPTPROFILE 標記,其中包含元數據,比如表示所用 COPS 的版本的屬性。
清單 3 展示了全局優化准則部分中的一個示例准則。該例中的准則指定應該被設置為 ALWAYS 的 REOPT 綁定選項。REOPT 綁定選項將影響其中包含參數標記或主機變量的語句的優化。將 REOPT 設置為 ALWAYS 後,將針對每次執行重新編譯語句。在一個有效概要文件中,至多有一個 Global Optimization Section。
語句概要文件部分(statement profile section)是可選的。在指定語句概要文件部分後,可以使用 STMTPROFILE 標記包含一個或多個語句概要文件,並包含適用於特定 SQL 查詢的准則。每個語句概要文件包含以下內容:
一個 STMTKEY 元素,它標識出必須應用准則的語句。
一個 OPTGUIDELINES 元素,描述准則本身。
STMTKEY 元素中定義的語句必須精確匹配其訪問計劃必須受到影響的語句,但是允許出現不同的空白。不支持在 STMTKEY 中使用通配符影響語句組。每一條需要影響的語句都要求具有一個單獨的 STMTPROFILE 部分。如果有超過一個 STMTPROFILE 部分匹配某個正在執行的語句,那麼將選擇並應用出現的第一個准則。清單 3 包含了一個優化准則,指定 STMTKEY 中的查詢應該選擇一個 XANDOR (XML Index Anding and Oring) 計劃。
由於 STMTKEY 元素中的語句包含特殊的 XML 字符 < and >,因此需要用一個 CDATA 部分將查詢文本包圍起來,CDATA 的開始部分為 <![CDATA[,結束部分為 ]]>,如上面的 清單 3 所示。
從優化准則中的查詢引用表
優化概要文件中的 guidelines 部分使用戶能夠為特定表指定一個操作,比如針對該表的訪問方法,或者將表指定為一個連接的 outer leg 或 inner leg,等等。重要的是要正確地識別您希望影響的表。可以根據表名或經過優化後的 SQL 語句中的限定符(quantifIEr)編號識別表,SQL 語句顯示在由 db2exfmt 工具生成的執行計劃中。使用表名比較簡單,但是使用表標識符的功能更加強大,因為它可以指代一個派生的限定符,比如一個 “XMLexists” 謂詞。
根據表名識別表
考慮以下的簡單查詢:
SELECT * FROM security WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/StockInformation[Industry= "OfficeSupplIEs"]')
本例中的公開的表名為 “SECURITY”。該查詢的准則應當指定這個公開的表名。例如,一個使用索引 “SEC_INDUSTRY” 的 XML 索引訪問可以這樣指定:
<XISCAN TABLE='SECURITY' INDEX='SEC_INDUSTRY'/>
現在考慮下面的查詢,將對表名使用 “sec” 作為別名:
SELECT * FROM security sec WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/StockInformation[Industry= "OfficeSupplIEs"]')
公開的表名為 “SEC”,並且需要修改索引准則,使其引用這個公開的表名,如下所示:
<XISCAN TABLE='SEC' INDEX='SEC_INDUSTRY'/>
根據 TABID 識別表
當查詢被提交到 DB2 後,將以一種最適合優化的方式重寫它。重寫後的查詢也稱為 “Optimized SQL”,它將在使用 db2exfmt 命令解釋語句時出現。優化後的 SQL 位於 db2exfmt 輸出的 “Optimized Statement” 部分。例如,對於清單 4 中的查詢:
清單 4. 查詢
SELECT *
FROM order, security
WHERE order.security_id = security.security_id
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]');
優化後的語句可能類似如下所示:
清單 5. 優化後的語句
SELECT Q3.ID AS "ID", Q3.SECURITY_ID AS "SECURITY_ID", Q3.ODOC AS "ODOC", Q2.ID
AS "ID", Q2.SECURITY_ID AS "SECURITY_ID", Q2.SDOC AS "SDOC"
FROM $INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
('(($INTERNAL_XMLTOXML_NIEO$(Q2.SDOC))[
$INTERNAL_EBV_BOOLEAN$(Security/Symbol[(. = "IBM")])])(:-->$C0:)')))
AS Q1), TPOX.SECURITY AS Q2, TPOX.ORDER AS Q3
WHERE (Q3.SECURITY_ID = Q2.SECURITY_ID)
SECURITY 表的內部編號為 Q2;對於 ORDER,其編號為 Q3。初始查詢中的 XMLEXISTS 謂詞被轉換為一個復雜表達式,可以通過編號 Q1 引用。准則將首先對 SECURITY 表應用 XMLexists 謂詞,隨後是與 ORDER 表的 hash 連接,如清單 6 所示:
清單 6. 根據名稱或標識符引用表
<?XML version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Identifying the exposed name of a table">
<STMTKEY SCHEMA="TPOX">
SELECT *
FROM order, security
WHERE order.security_id = security.security_id
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<HSJOIN>
<NLJOIN FIRST='TRUE'>
<Access TABLE='SECURITY'/>
<Access TABID='Q1'/>
</NLJOIN>
<Access TABID='Q3' />
</HSJOIN>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
清單 6 表明准則中可能同時包含了表名和表標識符。表名通過 XML 屬性 TABLE 指定,而表標識符通過 XML 屬性 TABID 指定。
創建 OPT_PROFILE 表
優化概要文件必須被存儲在 SYSTOOLS.OPT_PROFILE 表中。
可以使用兩種方法創建該表:
DB2 提供了一個由系統定義的存儲過程,稱為 SYSINSTALLOBJECTS,它可以創建或刪除 OPT_PROFILE 表:
db2 "call sysinstallobjects('opt_profiles', 'c', '', '')"
SYSINSTALLOBJECTS 過程將創建或刪除用於各種工具的數據庫對象。第一個參數表明用戶對創建優化概要文件表感興趣。第二個參數表明目標用戶操作是創建 優化概要文件表。第三個和第四個參數指定模式名和對象名,這兩個參數必須留空,這樣就將使用默認的模式 SYSTOOLS 和表名 OPT_PROFILE。和所有系統過程一樣,SYSINSTALLOBJECTS 過程位於 SYSPROC 模式中。
此外,可以顯式地發出以下 DDL 語句來創建 OPT_PROFILE 表:
清單 7. 創建 OPT_PROFILE 表的 DDL 語句
create table systools.opt_profile (
schema varchar(128) not null,
name varchar(128) not null,
profile blob (2m) not null,
primary key (schema, name)
)
schema 列用於指定優化概要文件的模式名。模式名必須是由數字和字母組成的字符串,並且必須遵守相應 DB2 LUW 發行版中的模式名的所有命名規則。
name 列包含優化概要文件的名稱,還可能包含最多由 128 個字符組成的字母數字式字符串。
profile 列包含 XML 文檔,其中包含優化概要文件。
加載概要文件
在啟用優化概要文件之前,必須將其關聯到一個惟一的模式限定名,並存儲在 SYSTOOLS.OPT_PROFILE 表中。可以使用 INSERT 語句、IMPORT 實用工具或 LOAD 實用工具。清單 8 解釋了如何將一個優化概要文件插入到 OPT_PROFILE 表中。此概要文件為 SECURITY 表包含了一個 XISCAN 准則。
清單 8. 插入一個優化概要文件
insert into systools.opt_profile values
('TPOX','PROFILE1',
CAST('<?XML version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.00">
<STMTPROFILE ID="Listing 3">
<STMTKEY><![CDATA[SELECT * FROM security
WHERE XMLEXISTS(''$SDOC/Security/SecurityInformation/
StockInformation[Industry="OfficeSupplIEs"]'')]]>
</STMTKEY>
<OPTGUIDELINES>
<XISCAN TABLE="SECURITY"/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>' as blob));
此外,用戶還可以使用 IMPORT 實用工具導入已被保存為 XML 文件的優化概要文件。
下面的示例展示了如何將兩個概要文件從 weekly_report_profile.xml 和 scientist_querIEs_profile.xml 文件導入到 SYSTOOLS.OPT_PROFILE 表。假設這兩個概要文件位於當前目錄中。一個帶分隔符的輸入文件,比如 “profiledata”(參見 清單 9),包含概要文件模式、概要文件名和 XML 文件名。注意每個概要文件必須出現在單獨的行中。
清單 9. 輸入文件中的概要文件數據
"TPOX","PROFILE1","weekly_report_profile.XML"
"TPOX","PROFILE2","scientist_querIEs_profile.XML"
現在,可以使用 IMPORT 命令將概要文件導入到 OPT_PROFILE 表。
清單 10. 將概要文件導入到 OPT_PROFILE 表
import from profiledata of del
modifIEd by lobsinfile
insert into systools.opt_profile
啟用概要文件
可以將任意數量的概要文件加載到 OPT_PROFILE 表。然而,在任何指定時間,最多只有一個概要文件是有效的。如果需要影響多個查詢,可在一個優化概要文件中包含多個語句概要文件。然後在包級別使用 OPTPROFILE 綁定選項,或者在會話級別使用 CURRENT OPTIMIZATION PROFILE 專用寄存器,從而啟用優化概要文件。CLI 應用程序可以使用客戶機配置選項 CURRENTOPTIMIZATIONPROFILE 來對每個連接使用專用寄存器。
例如,用戶可以發出以下命令:
db2 "set current optimization profile='TPOX.PROFILE1'"
這使 PROFILE1 在模式 TPOX 中充當有效概要文件,直到當前會話結束。在任何時候,都可以使用 set 命令指定專用寄存器的值。
可以使用以下命令告訴優化器不要使用任何優化配置文件。優化器將通過基於成本的決策返回最佳計劃。
db2 "set current optimization profile=''"
各種選項的優先順序如下所示:
不管使用何種設置,OPTPROFILE 綁定選項適用於所有靜態語句。
對於靜態語句,CURRENT OPTIMIZATION PROFILE 專用寄存器的值根據以下順序確定:
應用程序中的最後一個 SET CURRENT OPTIMIZATION PROFILE 語句 [最高優先級]
CURRENTOPTIMIZATIONPROFILE 客戶機配置選項,如果已設置的話
OPTPROFILE 綁定選項,如果已設置的話
發出查詢
啟用所需的概要文件後,用戶就可以不加修改地運行他們的工作負載。對於匹配當前概要文件的查詢,優化器將盡最大的努力來生成准則所要求的計劃。對於不與任何准則匹配的查詢,將選擇常規的基於成本的訪問計劃。
檢驗准則是否得到執行
DB2 Explain 工具對於判斷某個概要文件是否成功匹配查詢非常有用。在解釋輸出的 “Profile Information” 部分,指定了合適的優化概要文件名和語句概要文件名。
清單 11. 概要文件信息
OPT_PROF: (Optimization Profile Name)
TPOX.PROFILE1
STMTPROF: (Statement Profile Name)
Listing 3
db2exfmt 輸出的 “Profile Information” 部分可以用來檢驗是否已將正確的優化概要文件和語句概要文件應用到當前的查詢。
如果優化器無法應用准則,那麼它將返回警告 437,其原因代碼為 13:
SQL0437W Performance of this complex query may be sub-optimal. Reason code: "13". SQLSTATE=01602
隨後可以解釋查詢並檢查 db2exfmt 輸出來獲得更多診斷信息,以便修復有關准則的任何問題。例如,如果沒有找到引用的表名並因而未能應用准則的話,那麼 db2exfmt 將生成以下輸出:
清單 12. 更多診斷信息
Diagnostic IdentifIEr: 1
Diagnostic Details: EXP0009W Invalid Access request. The table
reference identifIEd by the TABLE attribute could
not be found. Line number "1", character number
"60".
如果沒有出現任何警告信息或診斷消息,那麼表明優化器已經成功應用了准則。可以查看 db2exfmt 輸出,了解所使用的訪問/連接方法。
XML 准則的使用場景
影響訪問方法 / 強制實施 XML 索引訪問
XISCAN (XML Index Scan) 准則
該准則指定一個單一的 XML 索引掃描,將用於訪問給定的表。可以在一個優化准則中包含以下的 XISCAN 元素,表明應當使用索引 “SEC_INDUSTRY” 訪問表 “SECURITY”:
<XISCAN TABLE='SECURITY' INDEX='SEC_INDUSTRY' FIRST="TRUE"/>
或 TABID 屬性指定了表名和表 id(即表的標識符,顯示在被訪問的 db2exfmt 輸出的優化 SQL 中。這個屬性是必需的)。
TABLE
INDEX 屬性是可選的,可用於指定一個索引名。如果指定了索引名的話,它將對特定的 XML 索引使用 XISCAN 操作符來請求一個訪問計劃。如果未指定索引名,優化器將選用成本最低的 XML 索引訪問計劃。
FIRST 屬性也是可選的。如果指定了的話,它將只接受 “TRUE” 值,表示給定表必須是連接查詢中進行訪問的第一個表。對於 FROM 子句中列出的所有表,最多有一個訪問或計劃可以帶有 “FIRST” 屬性。