提高DB2資料庫中的終極SQL性能調節技術

    對DB2優化管理器（otimizer）而言,如何更加有效的執行SQL語句是由存在於DB2 catalog中的統計信息決定的,優化器利用這些信息決定最優化的路徑.
　　使用針對工作負載的正確的性能調節技術,以避免硬體升級和優化DB2性能

　　性能通過響應時間,吞吐量,峰值響應時間,命中和每秒會話來衡量.SQL編碼和調節技術直接影響性能.開發高性能的DB2應用需要對DB2技術的深入了解.

　　當然在小數據量時這些技術無足輕重.忽略的連接,子查詢,表的表達式和CASE表達式的程序完全可以在輕量級負載下工作的很好.使用100%的SELECT INFO語句來進行數據獲取的程序,在開始會非常的迅速.但是一旦數據量和會話速度增加,性能將受到很大影響.DB2的可擴展性需要小的,優化的SQL加上方案設計,性能結構,緩衝池,和針對工作負載模式優化的存儲.另外的方案就是升級硬體了.當然對於有著硬體升級的無盡預算的人來說,不用閱讀本文了.對於其他人,我將講解如何編碼聰明的SQL以及調優的訪問路徑.

　　指針對於DB2性能的影響

　　曾經有段時間,在一個大的複雜的銀行應用程序中存在著一個批處理程序.這個新的批處理程序和訪問路徑被通過代碼走查的方式檢查過了.因為項目截止日期的原因測試很少; 在實際的首次運行中,程序在運行10個小時之後終止了.一個很慢的代碼走查之後,發現了7個指針,每個指針訪問一個不同的表中的數據.每個指針在其他打開的指針的循環中被打開,在彼此間傳遞數據.也就是說,這個程序在DB2以外竟然結合了7個表.這不是聰明的SQL.這個信息需要進入到7個表; 然而,每個指針只能進入一個.因此,7個指針被合併為一個聰明的指針：　

SELECT COL1, COL2, rest of the columnsFROM ADDR A, NAME N, T3, T4, T5, T6, T7WHERE A.COL1 = N.COL9AND N.COL9 = T3.COL3AND T3.COL3 = T4.COL4AND T4.COL4 <> T5.COL5AND T4.COLX <> T5.COLYAND T5.COL6 = T6.COL6AND T6.COL6 = T7.COL7AND T6.CODE = :hv

　　這個批處理在第二天用了四分鐘就完成了.大多數人可能會結束這個成功的任務了,但是務實的人不會.一個緩慢的EXPLAIN信息走查發現了一個有趣的表連接序列問題.優化器選擇了開始7個表的複雜的循環連接,還使用了一系列的大的數據表(ADDR和NAME),它們每個都包含5千萬行數據.這不是DB2優化器的典型行為.然而,有一些使用<>比較小表之間列的連接情況.這些比較對於優化器來說很難估計,因為DB2 catalog包含了相等列而非不等列.這裡就需要訪問路徑優化了.DB2優化者腦中肯定有多種推薦的解決方案,一些可以在包或語句層次上,另外的一些工作在謂詞層次.當然還有其他一些傳統方式不奏效情況下的終極技術.一個要求就是如下的性能調節技術提供給你的catalog以足夠的統計,使用統計嚮導來保證優化器有關於你的數據的精確全景.

　　DB2性能調節技術

　　包級別的SQL調優——需要REOPT(ONCE/ALWAYS/AUTO) BIND選項.這個語句通告優化器來在運行時重新優化包中的每個語句,至少ONCE,或者ALWAYS(每次執行),在DB2 9中可以AUTO(需要時).這項技術的開銷由選擇的選項和SQL語句的數量及複雜性決定.這些開銷在批處理程序中可以忽略不計,但是在短期運行的交易中會有很大影響.在我們的例子中,批處理程序指針只有一個謂詞和一個基數為1的主機變數.REOPT是一個調節選項,用來優化非統一列值分佈和主機變數內容高可變的情況,是COLCARDF=1的反面.包級別的調節並不合適.

　　語句級別的調節技術——包括OPTIMIZE FOR n ROWS和FETCH FIRST n ROWS ONLY.這些語句,放在SELECT語句末尾,是在不需要結果集的情況下進行優化的.優化器假設除了這些語句的所有的SELECT語句需要整個結果,這些結果偏向於諸如數序和表預取的訪問路徑.因為我們的批處理指針一定需要整個結果,因此語句級別的調節也不是合適的技術.

　　謂詞界別的調節技術——包括增加一個假的過濾器(TX.CX=TX.CX)或增加一個空操作到謂詞上( 0,-0,/1,*1, CONCAT 『』).一個假的過濾器能夠通過減少總過濾器因素(表中滿足資格的行的比例)改變優化器.這個方法能夠改變表連接的順序,索引選擇和連接方法.多個假過濾器是允許的,但是必須在沒有引用過的一列上.空操作(no op)能夠通過降級一個過濾器從符合到不符合來改變優化器的工作方式,但是只在z/OS上有用,LUW優化器卻不受其影響.這個改變也會影響一個表連接序列,索引選擇和連接方法.謂詞級別的技術可以被一起使用來獲取想要的結果.我們例子中的指針對多個謂詞級別調節的結合不起反應,因此是採用重武器的時候了.

　　一些終極調節技術包括使用DISTINCE的表的表達式和其他終極跨查詢的塊優化方法.這些技術要求手動查詢重寫.它們強制是的優化器以一個指定順序的方式執行查詢塊.使用這些技術視需要終極提醒的,因為他們能把表連接序列,索引選擇和連接方法從好改到壞.DISTINCE表表達式強制優化器優先於其他查詢塊執行圓括弧中的查詢.如果SELECT DISTINCE中指定的列引用了不同的表,表表達式可以被實例化為唯一的以供排序.我們的批處理指針有一個非優化的連接序列,使用該技術得到如下查詢：　　

SELECT All columns needed FROM ADDR, NAME, (SELECT DISTINCT columns from tables 3 through 7FROM T3, T4, T5, T6, T7WHERE join conditions T3 through T7AND T6.CODE =:hv) AS TEMPWHERE join conditions for ADDR, NAME and TEMP

　　這樣的查詢重寫迫使優化器通過T7連接表T3來連接ADDR和NAME.如果關鍵字DISTINCT在上例中省略了,DB2優化器合併表表達式查詢和輸出查詢,這樣就和原來的語句和連接序列一樣了.SELECT DISTINCT是一個關鍵的組件.然而,因為列列表跨越了多個表,臨時的5個表連接結果實例為一個唯一的工作文件以供排序.排序的開銷平均在每次執行幾千行,這是可以忽略的負載.批處理程序現在可以在兩分鐘之內完成任務了.

　　更多未來的調節技術

　　其他的一些查詢重寫技術從全異的查詢塊中獲取信息,以重寫查詢.IBM曾經將此技術成為跨查詢塊優化; DB2 9中被成為全局優化.一個好消息就是這項技術開始在DB2優化器的自我查詢重寫(QWR)階段中出現了.所有DB2查詢都能使用它也是指日可待了.同時,我們也需要將一些終極方法掌握在自己的手裡.

Tags: