DB2性能優(yōu)化之設(shè)計(jì)DB2配置數(shù)據(jù)庫(kù)很好用！
在這里我們主要向大家講述的是DB2性能優(yōu)化之設(shè)計(jì)DB2配置數(shù)據(jù)庫(kù)的正確操作步驟，在實(shí)際操作中有很多數(shù)據(jù)庫(kù)設(shè)計(jì)與配置選項(xiàng)時(shí)直接影響查詢DB2數(shù)據(jù)庫(kù)的性能。對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)的更多建議參考“ Planning your Physical Database Design ”***實(shí)踐文章。
使用約束來(lái)提高查詢優(yōu)化
考慮定義的唯一性，檢查并參考一致性約束。這些約束提供了語(yǔ)義信息，允許 DB2 優(yōu)化器重寫(xiě)查詢來(lái)評(píng)估連接，通過(guò)連接來(lái)降低聚合和 FETCH FIRST N ROWS，去掉不必要的 DISTINCT 選項(xiàng)被和一些其它的DB2性能優(yōu)化。
當(dāng)應(yīng)用程序可以保證它自己的關(guān)系時(shí)，信息約束也可以被用來(lái)檢查并參考一致性約束。相同的優(yōu)化也是可以的。當(dāng)更新(插入或刪除)行的時(shí)候，來(lái)自數(shù)據(jù)庫(kù)管理器的強(qiáng)制約束可能導(dǎo)致很高的系統(tǒng)開(kāi)銷，尤其在更新很多有一致性約束的行的時(shí)候。如果一個(gè)應(yīng)用程序在更新一行之前已經(jīng)驗(yàn)證的信息，這樣使用信息約束比起正常的約束更有效
例如，考慮 2 個(gè)表 DAILY_SALES 和 CUSTOMER 。在 CUSTOMER 表中的每一行都有一個(gè)唯一的客戶鍵值(CUST_KEY)。 DAILY_SALES 包含一個(gè) CUST_KEY 列并且每一行都引用一個(gè) CUSTOMER 表中的客戶鍵?？梢詣?chuàng)建一個(gè)參考一致性約束來(lái)防止在 CUSTOMER 和 DAILY_SALES 之間發(fā)生 1:N 的關(guān)系。
如果應(yīng)用程序要強(qiáng)制約束這個(gè)關(guān)系，可以創(chuàng)建一個(gè)信息化的約束。那么下面的查詢避免了在 CUSTOMER 和 DAILY_SALES 之間進(jìn)行連接，因?yàn)闆](méi)有從 CUSTOMER 獲取任何列，而且來(lái)自于 DAILY_SALES 的每一行都可以在 CUSTOMER 里面找到與之匹配的行，所以查詢優(yōu)化器將自動(dòng)刪除連接
- SELECT AMT_SOLD, SALE PRICE, PROD_DESC
- FROM DAILY_SALES, PRODUCT, CUSTOMER
- WHERE
- DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY AND
- DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY
應(yīng)用程序必須執(zhí)行信息約束，否則查詢可能返回不正確的結(jié)果。在上面的例子中，如果行存在于 DAILY_SALES 中，在 CUSTOMER 表中卻找不到相應(yīng)的客戶鍵，那么上面的查詢返回的行可能不正確。
在復(fù)雜查詢中使用 REOPT 綁定選項(xiàng)和輸入變量
在一個(gè)在線事務(wù)處理(OLTP)環(huán)境的中輸入變量有較好的語(yǔ)句準(zhǔn)備時(shí)間是關(guān)鍵，在這樣的環(huán)境中語(yǔ)句往往比較簡(jiǎn)單而且查詢計(jì)劃選擇也很簡(jiǎn)單。使用不同的輸入變量多次運(yùn)行相同的語(yǔ)句可以復(fù)用在動(dòng)態(tài)語(yǔ)句高速緩存中編譯了的訪問(wèn)片段，避免了由于隨時(shí)更改輸入值而造成昂貴的 SQL 語(yǔ)句編譯開(kāi)銷。
然而，輸入變量對(duì)復(fù)雜的查詢負(fù)載也會(huì)造成問(wèn)題，它們的查詢計(jì)劃選擇非常復(fù)雜，因此優(yōu)化器需要更多的信息來(lái)做出好的決定。而且，語(yǔ)句編譯時(shí)間通常是總運(yùn)行時(shí)間中的一個(gè)很小組成部分。因?yàn)?BI 查詢通常不會(huì)重復(fù)，所以并沒(méi)有從動(dòng)態(tài)語(yǔ)句高速緩存上得到好處。
如果在一個(gè)復(fù)雜查詢工作負(fù)載中需要使用輸入變量，請(qǐng)考慮使用 REOPT(ALWAYS) BIND 選項(xiàng)。當(dāng)輸入變量值是已知的，REOPT BIND 選項(xiàng)從 PREPARE 到 OPEN 或執(zhí)行過(guò)程中推遲了語(yǔ)句編譯。變量值被傳遞到 SQL 編譯器中，這樣優(yōu)化器可以使用這些便利來(lái)計(jì)算一個(gè)更精確的選擇評(píng)估。 REOPT(ALWAYS) 表示所有執(zhí)行語(yǔ)句都應(yīng)該被預(yù)編譯。
REOPT(ALWAYS) 也可以被用于涉及特殊寄存器的復(fù)雜查詢，比如 "WHERE TRANS_DATE = CURRENT DATE - 30 DAYS" 。如果輸入變量對(duì) OLTP 工作負(fù)載造成較差的訪問(wèn)計(jì)劃選擇，并且 REOPT(ALWAYS) 選項(xiàng)因?yàn)檎Z(yǔ)句編譯造成過(guò)多的開(kāi)銷，那么考慮對(duì)挑選過(guò)的查詢使用 REOPT(ONCE) 。 REOPT(ONCE) 推遲語(yǔ)句的編譯直到***數(shù)據(jù)變量被綁定。
使用這個(gè)***輸入變量值編譯并優(yōu)化 SQL 語(yǔ)句。后續(xù)使用不同的值來(lái)運(yùn)行的語(yǔ)句將重用基于***個(gè)輸入編譯的查詢片段。這是一個(gè)好方法 , 如果***輸入變量代表了后續(xù)的輸入值，并且在輸入值未知的情況下比起優(yōu)化器使用不同的值進(jìn)行評(píng)估，它提供個(gè)了一個(gè)更好的查詢?cè)L問(wèn)計(jì)劃 .
有很多方法來(lái)指定 REOPT：
對(duì) C/C++ 應(yīng)用程序中的嵌入式 SQL，使用 REOPT BIND 選項(xiàng)。這個(gè) BIND 選項(xiàng)影響靜態(tài)和動(dòng)態(tài) SQL 的再DB2性能優(yōu)化行為。
對(duì) CLP 包，用 REOPT 綁定參數(shù)重新綁定 CLP 包。例如，使用 CS 隔離級(jí)別和 REOPT ALWAYS 來(lái)重新綁定 CLP 包，詳細(xì)命令：
- rebind nullid.SQLC2G13 reopt always;
對(duì)使用傳統(tǒng) JDBC 驅(qū)動(dòng)的 CLI 應(yīng)用程序或 JDBC 應(yīng)用程序，在 db2cli.ini 中設(shè)置 REOPT 關(guān)鍵字。選項(xiàng)的值是：
2 - NONE
3 - ONCE
4 - ALWAYS
對(duì)于使用 JCC 通用驅(qū)動(dòng)的 JDBC 應(yīng)用程序，使用下面的方法之一：
使用 SQLATTR_REOPT 連接或語(yǔ)句屬性。
使用 SQL_ATTR_CURRENT_PACKAGE_SET 連接或語(yǔ)句屬性來(lái)制定 NULLID、NULLIDR1 或 NULLIDRA 包集合。 NULLIDR1 和 NULLIDRA 是保留的包集合名稱。一旦使用就分別隱含了 REOPT ONCE 和 REOPT ALWAYS 。這些包集合需要于那個(gè)下面命令顯示的創(chuàng)建： db2 bind db2clipk.bnd collection NULLIDR1;
- db2 bind db2clipk.bnd collection NULLIDRA;
對(duì) SQL PL 存儲(chǔ)過(guò)程使用下面的方法之一：
使用 SET_ROUTINE_OPTS 存儲(chǔ)過(guò)程來(lái)為在當(dāng)前會(huì)話中創(chuàng)建 SQL PL 存儲(chǔ)過(guò)程設(shè)置綁定選項(xiàng)，例如調(diào)用 sysproc.set_routine_opts( ‘ reopt always ’ )
使用 DB2_SQLROUTINE_PREPOPTS 注冊(cè)表變量在實(shí)例級(jí)別設(shè)置 SQL PL 存儲(chǔ)過(guò)程選項(xiàng)。值設(shè)置為使用 SET_ROUTINE_OPTS 存儲(chǔ)過(guò)程將覆蓋 DB2_SQLROUTINE_PREPOPTS 指定的值
你可也能使用優(yōu)化配置來(lái)為靜態(tài)語(yǔ)句和動(dòng)態(tài)語(yǔ)句設(shè)置 REOPT，如下面例子顯示的：
- <STMTPROFILE ID="REOPT example "> <STMTKEY>
- <![CDATA[select acct_no from customer where name = ? ]]>
- </STMTKEY>
- <OPTGUIDELINES>
- <REOPT VALUE='ALWAYS'/>
- </OPTGUIDELINES>
- </STMTPROFILE>
為你的工作負(fù)載選擇***的優(yōu)化級(jí)別
設(shè)置優(yōu)化級(jí)別可以獲得顯式指定優(yōu)化技術(shù)的好處，尤其出于下面的原因：
為了管理非常小的數(shù)據(jù)庫(kù)或者非常簡(jiǎn)單的查詢語(yǔ)句
為了在你的數(shù)據(jù)庫(kù)服務(wù)器編譯時(shí)進(jìn)行內(nèi)存限制
為了減少查詢編譯時(shí)間，比如 PREPARE
大多數(shù)語(yǔ)句可以通過(guò)使用第 5 級(jí)優(yōu)化得到充分的優(yōu)化和合理的資源，這也是默認(rèn)的查詢優(yōu)化級(jí)別。在一個(gè)給定的優(yōu)化級(jí)別，查詢編譯時(shí)間和資源消耗是主要受查詢復(fù)雜度的影響，尤其是連接以及子查詢的數(shù)目。不過(guò)，編譯時(shí)間和資源的使用同樣受到執(zhí)行優(yōu)化的影響。
查詢優(yōu)化級(jí)別 1，2，3，5 和 7 適用于一般用途。只有你需要進(jìn)一步減少查詢優(yōu)化時(shí)間而且在你知道 SQL 語(yǔ)句非常簡(jiǎn)單的情況下才考慮級(jí)別 0 。
Tip：要分析一個(gè)運(yùn)行很長(zhǎng)時(shí)間的查詢，對(duì)查詢運(yùn)行 db2batch 來(lái)找出花了多少時(shí)間在編譯上在運(yùn)行上花費(fèi)了多少時(shí)間。如果編譯需要更多的時(shí)間，降低優(yōu)化級(jí)別。如果執(zhí)行需要更多的時(shí)間那么就考慮更高的優(yōu)化級(jí)別
當(dāng)你選擇了一個(gè)優(yōu)化級(jí)別，考慮下面的一般準(zhǔn)則：
從使用默認(rèn)查詢優(yōu)化級(jí)別開(kāi)始，級(jí)別 5
要使用默認(rèn)級(jí)別之外的級(jí)別，首先嘗試級(jí)別 1，2 或 3 。級(jí)別 0，1 和 2 使用貪婪連接枚舉運(yùn)算法則。
如果你有很多表以及在同一列上有大量的連接謂詞，在關(guān)心編譯時(shí)間的情況下使用DB2性能優(yōu)化級(jí)別 1 或 2 。
對(duì)只有不到一秒的運(yùn)行時(shí)間的查詢使用一個(gè)低的優(yōu)化級(jí)別(0 或 1)。比如查詢往往有下面的特點(diǎn)：
只訪問(wèn)一個(gè)或很少的表
只獲取一行或者幾行
使用完全唯一的索引
在線事務(wù)處理(OLTP)事務(wù)是這種類型訪問(wèn)的很好例子
對(duì)長(zhǎng)時(shí)間運(yùn)行(超過(guò) 30 秒)的語(yǔ)句使用高一些的優(yōu)化級(jí)別(3，5 或 7)。
優(yōu)化級(jí)別 3 及其以上使用動(dòng)態(tài)編程連接枚舉算法。這個(gè)算法考慮更多的可選計(jì)劃，并且可能招致比 0，1，和 2 更多的編譯時(shí)間，尤其在表的數(shù)目增加后。
只有在你對(duì)一個(gè)查詢有特別的優(yōu)化需求時(shí)才使用優(yōu)化級(jí)別 9 。
復(fù)雜查詢需要不同數(shù)量的優(yōu)化來(lái)選擇***訪問(wèn)計(jì)劃。對(duì)有下面特征的查詢，請(qǐng)考慮使用更高的DB2性能優(yōu)化級(jí)別,
以上的相關(guān)內(nèi)容就是對(duì)的介紹，望你能有所收獲。
【編輯推薦】
