Prayer

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

先轉載一下關于db2優化器的官方資料：

優化級別 n=0,1,2,3,5,7,9

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

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

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

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

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

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

當查詢優化器減少查詢優化量時，它繼續應用正常時應用的所有查詢重寫規則。但是，它的確使用了貪婪聯合枚舉法并減少了考慮的存取方案的組合數。 

對于由事務和復雜查詢組成的混合環境，查詢優化級別 5 是一個很好的選擇。此優化級別設計成可以用高效的方式應用最有價值的查詢變換和其它查詢優化技術。 

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

---

調整優化器級別案例：

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

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

  

2、一個修正數據的腳本，含有大量的復雜sql如join、union、group等操作，在執行時同樣報sql0437W的警告，經降低優化器級別后，問題解決。
相關語句：

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


d:/>db2 ? 01602

SQLSTATE 01602: 優化級別已降低。


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

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


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

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


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

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


d:/>