db2缺省的優(yōu)化器級(jí)別為5，級(jí)別越高優(yōu)化程度越高，大多數(shù)情況下為應(yīng)用程序綁定時(shí)指定高級(jí)別的優(yōu)化器會(huì)提高性能，但有時(shí)因機(jī)器硬件或內(nèi)存分配不合適，高級(jí)別的優(yōu)化會(huì)消耗更多的資源，有時(shí)會(huì)優(yōu)化報(bào)錯(cuò)。本人碰到過幾次因優(yōu)化器不能正常工作反而使sql的性能急劇下降的案例。
先轉(zhuǎn)載一下關(guān)于db2優(yōu)化器的官方資料：
優(yōu)化級(jí)別 n=0,1,2,3,5,7,9
可以在編譯 SQL 查詢時(shí)指定下列其中一個(gè)優(yōu)化器級(jí)別：
0 -
此級(jí)別指導(dǎo)優(yōu)化器使用最少的優(yōu)化來(lái)生成存取方案。此優(yōu)化級(jí)別具有下列特征：
優(yōu)化器不考慮任何非均勻分布統(tǒng)計(jì)信息。
僅應(yīng)用基本的查詢重寫規(guī)則。
發(fā)生貪婪聯(lián)合枚舉。
僅允許使用嵌套循環(huán)連接及索引掃描存取方法。
[9] 在生成的存取方法中不使用列表預(yù)取。
不考慮星型連接策略。
此級(jí)別應(yīng)該只用于需要最低的查詢編譯開銷的情況。查詢優(yōu)化級(jí)別 0 適用于以下應(yīng)用程序：完全由存取經(jīng)過良好索引表的很簡(jiǎn)單的動(dòng)態(tài) SQL 語(yǔ)句組成。
1 -
此優(yōu)化級(jí)別具有下列特征：
優(yōu)化器不考慮任何非均勻分布統(tǒng)計(jì)信息。
只應(yīng)用查詢重寫規(guī)則的一個(gè)子集。
發(fā)生貪婪聯(lián)合枚舉。
[9] 在生成的存取方法中不使用列表預(yù)取。
除了“合并掃描”連接及表掃描也可用以外，優(yōu)化級(jí)別 1 類似于級(jí)別 0。
2 -
此級(jí)別指導(dǎo)優(yōu)化器使用比級(jí)別 1 顯著高的優(yōu)化程度，而使復(fù)雜查詢的編譯成本顯著低于級(jí)別 3 及更高級(jí)別。此優(yōu)化級(jí)別具有下列特征：
利用了所有可用的統(tǒng)計(jì)信息，包括頻率和分位數(shù)非均勻分布統(tǒng)計(jì)信息。
除只在極少情況下才適用的計(jì)算密集型規(guī)則外，將應(yīng)用所有其它查詢重寫規(guī)則，包括路由對(duì)具體查詢表的查詢。
使用了貪婪聯(lián)合枚舉。
考慮各種存取方法，包括列表預(yù)取和具體查詢表路由。
如果適用的話，考慮星型連接策略。
優(yōu)化級(jí)別 2 除了使用“貪婪”聯(lián)合枚舉而不是“動(dòng)態(tài)規(guī)劃”以外，類似于級(jí)別 5。在所有使用“貪婪”聯(lián)合枚舉算法的級(jí)別中，此級(jí)別具有最高的優(yōu)化程度，與級(jí)別 3 及更高級(jí)別相比，它對(duì)復(fù)雜查詢的替代方案考慮較少，因而消耗的編譯時(shí)間也少。建議將級(jí)別 2 用于決策支持或聯(lián)機(jī)分析處理（OLAP）環(huán)境中非常復(fù)雜的查詢。在這種環(huán)境下，特定查詢很少完全重復(fù)，因此查詢存取方案不大可能在高速緩存中停留到出現(xiàn)下一個(gè)查詢?yōu)橹埂?nbsp;
3 -
此級(jí)別請(qǐng)求中等優(yōu)化。此級(jí)別與 DB2 MVS/ESA 版、OS/390 或 z/OS 版的查詢優(yōu)化特征基本匹配。此優(yōu)化級(jí)別具有下列特征：
使用非均勻分布統(tǒng)計(jì)信息（如果可用的話），該統(tǒng)計(jì)信息跟蹤頻繁出現(xiàn)的值。
應(yīng)用大部分查詢重寫規(guī)則，包括子查詢至連接的變換。
動(dòng)態(tài)規(guī)劃連接枚舉，如下所示：
組合內(nèi)部表的有限使用
涉及查找表的星型模式的笛卡爾乘積的有限使用
考慮各種存取方法，包括列表預(yù)取、索引 AND 運(yùn)算和星型連接。
此級(jí)別適用于大量應(yīng)用程序。此級(jí)別改進(jìn)具有 4 個(gè)或更多連接的查詢的存取方案。但是，優(yōu)化器可能無(wú)法考慮使用缺省優(yōu)化級(jí)別選擇的更好方案。
5 -
此級(jí)別指導(dǎo)優(yōu)化器使用相當(dāng)大量的優(yōu)化來(lái)生成存取方案。此優(yōu)化級(jí)別具有下列特征：
使用所有可用的統(tǒng)計(jì)信息，包括頻率和分位數(shù)分布統(tǒng)計(jì)信息。
除只在極少情況下才適用的那些計(jì)算密集型規(guī)則外，將應(yīng)用所有其它查詢重寫規(guī)則，包括路由對(duì)具體查詢表的查詢。
動(dòng)態(tài)規(guī)劃連接枚舉，如下所示：
組合內(nèi)部表的有限使用
涉及查找表的星型模式的笛卡爾乘積的有限使用
考慮各種存取方法，包括列表預(yù)取、索引 AND 運(yùn)算和具體查詢表路由。
當(dāng)優(yōu)化器檢測(cè)到不能保證用于復(fù)雜動(dòng)態(tài) SQL 查詢的附加資源和處理時(shí)間時(shí)，將減少優(yōu)化。減少的范圍或大小取決于機(jī)器大小和謂詞數(shù)目。
當(dāng)查詢優(yōu)化器減少查詢優(yōu)化量時(shí)，它繼續(xù)應(yīng)用正常時(shí)應(yīng)用的所有查詢重寫規(guī)則。但是，它的確使用了貪婪聯(lián)合枚舉法并減少了考慮的存取方案的組合數(shù)。
對(duì)于由事務(wù)和復(fù)雜查詢組成的混合環(huán)境，查詢優(yōu)化級(jí)別 5 是一個(gè)很好的選擇。此優(yōu)化級(jí)別設(shè)計(jì)成可以用高效的方式應(yīng)用最有價(jià)值的查詢變換和其它查詢優(yōu)化技術(shù)。
7 -
此級(jí)別指導(dǎo)優(yōu)化器使用相當(dāng)大量的優(yōu)化來(lái)生成存取方案。級(jí)別 7 除了不減少用于復(fù)雜動(dòng)態(tài) SQL 查詢的查詢優(yōu)化量以外，它與查詢優(yōu)化級(jí)別 5 是相同的。
9 -
此級(jí)別指導(dǎo)優(yōu)化器使用所有可用的優(yōu)化技術(shù)。這些技術(shù)包括：
所有可用的統(tǒng)計(jì)信息
所有查詢重寫規(guī)則
聯(lián)合枚舉的所有可能性，包括笛卡爾乘積和任意多種組合的內(nèi)部結(jié)構(gòu)
所有存取方法
此級(jí)別可以大大擴(kuò)展由優(yōu)化器考慮的可能的存取方案數(shù)量。對(duì)于使用大表的很復(fù)雜且運(yùn)行時(shí)間很長(zhǎng)的查詢，可以使用此級(jí)別來(lái)確定更全面優(yōu)化是否將生成更好的存取方案。使用“說(shuō)明”和性能測(cè)量來(lái)驗(yàn)證是否實(shí)際上已找到更好的方案。
調(diào)整優(yōu)化器級(jí)別案例：
1、中試所fmis3上線后發(fā)現(xiàn)業(yè)務(wù)模塊性能很慢，在調(diào)優(yōu)過程中發(fā)現(xiàn)大部分復(fù)雜的sql都報(bào)警告：
SQL0437W 此復(fù)合查詢的性能可能不是最優(yōu)的。原因碼為："3"。 SQLSTATE=01602
經(jīng)查幫助得知原因：3 優(yōu)化器成本下溢
估計(jì)是服務(wù)器的資源所限(CPU、內(nèi)存)，降低優(yōu)化級(jí)別，調(diào)整數(shù)據(jù)庫(kù)參數(shù)(DFT_QUERYOPT = 3)后問題解決。
相關(guān)語(yǔ)句：
$ db2 update db cfg for cwgl using DFT_QUERYOPT 3
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
$
2、一個(gè)修正數(shù)據(jù)的腳本，含有大量的復(fù)雜sql如join、union、group等操作，在執(zhí)行時(shí)同樣報(bào)sql0437W的警告，經(jīng)降低優(yōu)化器級(jí)別后，問題解決。
相關(guān)語(yǔ)句：
d:/>db2 -z test.log -std@ -f test.sql
SQL0437W 此復(fù)合查詢的性能可能不是最優(yōu)的。原因碼為："3"。 SQLSTATE=01602
d:/>db2 ? 01602
SQLSTATE 01602: 優(yōu)化級(jí)別已降低。
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:/>