Prayer

db2缺省的優(yōu)化器級別為5，級別越高優(yōu)化程度越高，大多數(shù)情況下為應(yīng)用程序綁定時指定高級別的優(yōu)化器會提高性能，但有時因機器硬件或內(nèi)存分配不合適，高級別的優(yōu)化會消耗更多的資源，有時會優(yōu)化報錯。本人碰到過幾次因優(yōu)化器不能正常工作反而使sql的性能急劇下降的案例。

先轉(zhuǎn)載一下關(guān)于db2優(yōu)化器的官方資料：

優(yōu)化級別 n=0,1,2,3,5,7,9

可以在編譯 SQL 查詢時指定下列其中一個優(yōu)化器級別： 

0 - 
此級別指導(dǎo)優(yōu)化器使用最少的優(yōu)化來生成存取方案。此優(yōu)化級別具有下列特征： 
優(yōu)化器不考慮任何非均勻分布統(tǒng)計信息。 
僅應(yīng)用基本的查詢重寫規(guī)則。 
發(fā)生貪婪聯(lián)合枚舉。 
僅允許使用嵌套循環(huán)連接及索引掃描存取方法。 
[9] 在生成的存取方法中不使用列表預(yù)取。 
不考慮星型連接策略。 
此級別應(yīng)該只用于需要最低的查詢編譯開銷的情況。查詢優(yōu)化級別 0 適用于以下應(yīng)用程序：完全由存取經(jīng)過良好索引表的很簡單的動態(tài) SQL 語句組成。 

1 - 
此優(yōu)化級別具有下列特征： 
優(yōu)化器不考慮任何非均勻分布統(tǒng)計信息。 
只應(yīng)用查詢重寫規(guī)則的一個子集。 
發(fā)生貪婪聯(lián)合枚舉。 
[9] 在生成的存取方法中不使用列表預(yù)取。 
除了“合并掃描”連接及表掃描也可用以外，優(yōu)化級別 1 類似于級別 0。 

2 - 
此級別指導(dǎo)優(yōu)化器使用比級別 1 顯著高的優(yōu)化程度，而使復(fù)雜查詢的編譯成本顯著低于級別 3 及更高級別。此優(yōu)化級別具有下列特征： 
利用了所有可用的統(tǒng)計信息，包括頻率和分位數(shù)非均勻分布統(tǒng)計信息。 
除只在極少情況下才適用的計算密集型規(guī)則外，將應(yīng)用所有其它查詢重寫規(guī)則，包括路由對具體查詢表的查詢。 
使用了貪婪聯(lián)合枚舉。 
考慮各種存取方法，包括列表預(yù)取和具體查詢表路由。 
如果適用的話，考慮星型連接策略。 
優(yōu)化級別 2 除了使用“貪婪”聯(lián)合枚舉而不是“動態(tài)規(guī)劃”以外，類似于級別 5。在所有使用“貪婪”聯(lián)合枚舉算法的級別中，此級別具有最高的優(yōu)化程度，與級別 3 及更高級別相比，它對復(fù)雜查詢的替代方案考慮較少，因而消耗的編譯時間也少。建議將級別 2 用于決策支持或聯(lián)機分析處理（OLAP）環(huán)境中非常復(fù)雜的查詢。在這種環(huán)境下，特定查詢很少完全重復(fù)，因此查詢存取方案不大可能在高速緩存中停留到出現(xiàn)下一個查詢?yōu)橹埂?nbsp;

3 - 
此級別請求中等優(yōu)化。此級別與 DB2 MVS/ESA 版、OS/390 或 z/OS 版的查詢優(yōu)化特征基本匹配。此優(yōu)化級別具有下列特征： 
使用非均勻分布統(tǒng)計信息（如果可用的話），該統(tǒng)計信息跟蹤頻繁出現(xiàn)的值。 
應(yīng)用大部分查詢重寫規(guī)則，包括子查詢至連接的變換。 
動態(tài)規(guī)劃連接枚舉，如下所示： 
組合內(nèi)部表的有限使用 
涉及查找表的星型模式的笛卡爾乘積的有限使用 
考慮各種存取方法，包括列表預(yù)取、索引 AND 運算和星型連接。 
此級別適用于大量應(yīng)用程序。此級別改進(jìn)具有 4 個或更多連接的查詢的存取方案。但是，優(yōu)化器可能無法考慮使用缺省優(yōu)化級別選擇的更好方案。 

5 - 
此級別指導(dǎo)優(yōu)化器使用相當(dāng)大量的優(yōu)化來生成存取方案。此優(yōu)化級別具有下列特征： 
使用所有可用的統(tǒng)計信息，包括頻率和分位數(shù)分布統(tǒng)計信息。 
除只在極少情況下才適用的那些計算密集型規(guī)則外，將應(yīng)用所有其它查詢重寫規(guī)則，包括路由對具體查詢表的查詢。 
動態(tài)規(guī)劃連接枚舉，如下所示： 
組合內(nèi)部表的有限使用 
涉及查找表的星型模式的笛卡爾乘積的有限使用 
考慮各種存取方法，包括列表預(yù)取、索引 AND 運算和具體查詢表路由。 
當(dāng)優(yōu)化器檢測到不能保證用于復(fù)雜動態(tài) SQL 查詢的附加資源和處理時間時，將減少優(yōu)化。減少的范圍或大小取決于機器大小和謂詞數(shù)目。 

當(dāng)查詢優(yōu)化器減少查詢優(yōu)化量時，它繼續(xù)應(yīng)用正常時應(yīng)用的所有查詢重寫規(guī)則。但是，它的確使用了貪婪聯(lián)合枚舉法并減少了考慮的存取方案的組合數(shù)。 

對于由事務(wù)和復(fù)雜查詢組成的混合環(huán)境，查詢優(yōu)化級別 5 是一個很好的選擇。此優(yōu)化級別設(shè)計成可以用高效的方式應(yīng)用最有價值的查詢變換和其它查詢優(yōu)化技術(shù)。 

7 - 
此級別指導(dǎo)優(yōu)化器使用相當(dāng)大量的優(yōu)化來生成存取方案。級別 7 除了不減少用于復(fù)雜動態(tài) SQL 查詢的查詢優(yōu)化量以外，它與查詢優(yōu)化級別 5 是相同的。
 
9 - 
此級別指導(dǎo)優(yōu)化器使用所有可用的優(yōu)化技術(shù)。這些技術(shù)包括： 
所有可用的統(tǒng)計信息 
所有查詢重寫規(guī)則 
聯(lián)合枚舉的所有可能性，包括笛卡爾乘積和任意多種組合的內(nèi)部結(jié)構(gòu) 
所有存取方法 
此級別可以大大擴展由優(yōu)化器考慮的可能的存取方案數(shù)量。對于使用大表的很復(fù)雜且運行時間很長的查詢，可以使用此級別來確定更全面優(yōu)化是否將生成更好的存取方案。使用“說明”和性能測量來驗證是否實際上已找到更好的方案。 

---

調(diào)整優(yōu)化器級別案例：

1、中試所fmis3上線后發(fā)現(xiàn)業(yè)務(wù)模塊性能很慢，在調(diào)優(yōu)過程中發(fā)現(xiàn)大部分復(fù)雜的sql都報警告：
   SQL0437W  此復(fù)合查詢的性能可能不是最優(yōu)的。原因碼為："3"。  SQLSTATE=01602
   經(jīng)查幫助得知原因：3 優(yōu)化器成本下溢
   估計是服務(wù)器的資源所限(CPU、內(nèi)存)，降低優(yōu)化級別，調(diào)整數(shù)據(jù)庫參數(shù)(DFT_QUERYOPT = 3)后問題解決。
   相關(guān)語句：
  

$ db2 update db cfg for cwgl using DFT_QUERYOPT 3
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
$
  

  

2、一個修正數(shù)據(jù)的腳本，含有大量的復(fù)雜sql如join、union、group等操作，在執(zhí)行時同樣報sql0437W的警告，經(jīng)降低優(yōu)化器級別后，問題解決。
相關(guān)語句：

d:/>db2 -z test.log -std@ -f test.sql
SQL0437W  此復(fù)合查詢的性能可能不是最優(yōu)的。原因碼為："3"。  SQLSTATE=01602


d:/>db2 ? 01602

SQLSTATE 01602: 優(yōu)化級別已降低。


d:/>db2 set current query optimization=3
DB20000I  SQL 命令成功完成。

d:/>db2 -z test.log -std@ -f test.sql
SQL0437W  此復(fù)合查詢的性能可能不是最優(yōu)的。原因碼為："3"。  SQLSTATE=01602


d:/>db2 set current query optimization=2
DB20000I  SQL 命令成功完成。

d:/>db2 -z test.log -std@ -f test.sql
SQL0437W  此復(fù)合查詢的性能可能不是最優(yōu)的。原因碼為："3"。  SQLSTATE=01602


d:/>db2 set current query optimization=1
DB20000I  SQL 命令成功完成。

d:/>db2 -z test.log -std@ -f test.sql
DB20000I  SQL 命令成功完成。


d:/>