SQL過程的性能：提示和技巧

使用 SQL 的一次處理一個集合語義

諸如循環、賦值和游標之類的過程化構造允許我們表達那些只使用 SQL DML 語句是不可能表達的計算。但是，當我們擁有一些可以隨意使用的過程語句時，即使我們手頭的計算實際上僅使用 SQL DML 語句就可表達，但轉換成過程語句還是有風險的。正如我們以前提到的，過程計算的性能與使用 DML 語句表達的同一個計算的性能相比會慢幾個數量級。請考慮下面的代碼片段：

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
IF (v1 > 20) THEN
INSERT INTO tab_sel VALUES (20, v2);
ELSE
INSERT INTO tab_sel VALUES (v1, v2);
END IF;
FETCH cur1 INTO v1, v2;
END WHILE;

首先，通過應用上一節討論的轉換可以改進循環體：

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
INSERT INTO tab_sel VALUES (CASE
WHEN v1 > 20 THEN 20
ELSE v1
END, v2);
FETCH cur1 INTO v1, v2;
END WHILE;

但是通過進一步觀察，我們發現整個代碼塊可以寫成一個帶有 SELECT 子句的 INSERT 語句：

INSERT INTO tab_sel (SELECT (CASE
WHEN col1 > 20 THEN 20
ELSE col1
END),
col2
FROM tab_comp);

在原始的表述中，SELECT 語句中每行的過程層和數據流層之間都有一個上下文切換。在最後一個表述中，根本沒有上下文切換，並且優化器有機會對整個計算進行全局優化。另一方面，如果每個 INSERT 語句針對的都是不同的表，那麼這種引人注目的簡化是不可能的，如下所示。

DECLARE cur1 CURSOR FOR SELECT col1, col2 FROM tab_comp;
OPEN cur1;
FETCH cur1 INTO v1, v2;
WHILE SQLCODE <> 100 DO
IF (v1 > 20) THEN
INSERT INTO tab_default VALUES (20, v2);
ELSE
INSERT INTO tab_sel VALUES (v1, v2);
END IF;
FETCH cur1 INTO v1, v2;
END WHILE;

但是，這裡也可以利用 SQL 的一次處理一個集合（set-at-a-time）特性：

INSERT INTO tab_sel (SELECT col1, col2
FROM tab_comp
WHERE col1 <= 20);
INSERT INTO tab_default (SELECT col1, col2
FROM tab_comp
WHERE col1 > 20);

在研究改進現有過程邏輯的性能時，為消除游標循環而花費的任何時間都可能是值得的。

改進游標性能

如果存儲過程中的邏輯確實需要游標，那麼要使性能最優，請牢記下面這些內容。

首先，請確保不使用高於您所需的隔離級別。隔離級別決定了 DB2 對過程讀取或更新的行應用的鎖定的數量。隔離級別越高，DB2 將執行的鎖定越多，因此為同一資源而競爭的應用程序之間的並發就越少。例如，使用可重復讀（Repeatable Read，RR）隔離級別的過程將形成對其讀取的任何行的共享鎖，而使用游標穩定性（Cursor Stability，CS）的過程只會鎖定任何可更新游標的當前行。可以使用 DB2_SQLROUTINE_PREPOPTS 注冊表變量來指定 SQL 過程的隔離級別。例如，要將 SQL 過程的隔離級別設置為未提交的讀（Uncommitted Read）（最低的級別，用於訪問只讀數據的過程），請使用下面這條命令：

db2set　DB2_SQLROUTINE_PREPOPTS="ISOLATION UR"

注：要使該設置生效，必須重新啟動 db2 實例。

DB2 中缺省的隔離級別是游標穩定性。但是，當然了，為了保持應用程序的正確性，有時需要使用可重復讀。還需記住一件重要的事情，一旦創建了需要可重復讀的過程，必須將 DB2_SQLROUTINE_PREPOPTS 重新設置回較低的隔離級別。

有關隔離級別還值得一提的是，DB2 允許我們在單獨的查詢中覆蓋缺省的隔離級別，如下所示：

DECLARE cur1 CURSOR FOR SELECT col1 FROM tab_comp WITH UR;

上面的查詢將以隔離級別 UR 進行執行，而不管 DB2_SQLROUTINE_PREPOPTS 中指定的隔離級別。

在嘗試改進游標性能時需要牢記的一個相關問題是游標的可更新能力。如果游標涉及的行是可以使用 INSERT 或 DELETE 語句中的 WHERE CURRENT OF 子句進行更新或刪除，那麼它就是 可刪除的。當游標可刪除時，DB2 必須獲取行上的 互斥鎖（與 共享鎖相對），並且不能執行行分塊。行上的互斥鎖甚至可以防止其它應用程序讀取該行（在互斥鎖被釋放之前，這些應用程序必須等待，除非它們的隔離級別是 UR），而行分塊通過在一個操作中檢索行塊，從而減少了用於游標的數據庫管理器開銷。

只有不可刪除的游標才可以進行行分塊。這就是為什麼讓 DB2 了解將如何使用游標是很重要的原因。通過在 SELECT 語句中指定 FOR READ ONLY 子句，可以將游標顯式地聲明為不可刪除，或者通過在 SELECT 語句中使用 FOR UPDATE 子句將其聲明為可刪除。根據該信息（並且還根據下面描述的 BLOCKING 選項），DB2 將確定是否將行分塊用於給定的游標。

缺省情況下，對於那些使用 FOR READ ONLY 子句定義的游標，DB2 將始終使用行分塊，除非指定了 BLOCKING NO 綁定選項。另一方面，如果使用了 BLOCKING ALL 綁定選項，那麼對於含混游標（既不是定義成 FOR READ ONLY 也不是定義成 FOR UPDATE 的游標），DB2 將使用行分塊。

簡而言之：如果可能，則在游標定義中使用 FOR READ ONLY 子句；如果您的過程包含含混游標，那麼請使用 BLOCKING ALL 綁定選項。要設置 BLOCKING 綁定選項的值，我們還可以使用 DB2_SQLROUTINE_PREPOPTS 注冊表變量。例如，要將 SQL 過程的隔離級別設置為未提交的讀，並將行分塊設置為 BLOCKING ALL，請使用下面這條命令：

db2set　DB2_SQLROUTINE_PREPOPTS="ISOLATION UR BLOCKING ALL"

對於返回大型結果集的過程而言，分塊特別重要。

通過使用 DB2_SQLROUTINE_PREPOPTS 注冊表，還可以為存儲過程指定其它綁定選項。

在無副作用的情況下，請使用 SQL 函數

正如我們在簡介中提及的，SQL 過程和 SQL 函數是使用不同技術實現的。SQL 過程中的查詢是單獨編譯的，每個查詢都成為包中的一個節。編譯是在過程創建時進行的，直到重新創建過程或者直到重新綁定其相關的包時才重新編譯這些查詢。

另一方面，SQL 函數中的查詢是一起編譯的，就好象函數體是一個查詢一樣。每當編譯一條使用 SQL 函數的語句時，也會對 SQL 函數進行編譯。

與 SQL 過程中所發生的情況不同，SQL 函數中的過程語句與數據流語句是在同一個層中執行的。因此，每當控制從過程語句流向數據流語句或相反時，並不發生上下文切換。

因為存在這些區別，所以當給定的過程代碼段作為函數實現時的執行速度通常比作為過程實現時要快。但是，當然了，有一個小問題。函數只能包含那些不會改變數據庫狀態的語句（例如 INSERT、UPDATE 或 DELETE 語句是不允許的）。並且只允許完整 SQL PL 語言的子集出現在 SQL 函數中（不能是 CALL 語句、游標和條件處理）。

盡管有這些限制，但大多數 SQL 過程都可以在無副作用的情況下轉換成 SQL 函數。例如，下面的過程：

CREATE PROCEDURE GetPrice (IN Vendor CHAR(20),
IN Pid INT,
OUT price DECIMAL(10,3))
LANGUAGE SQL
BEGIN
IF Vendor = 'Vendor 1' THEN
SET price = (SELECT ProdPrice
FROM V1Table　WHERE Id = Pid);
ELSE IF Vendor = 'Vendor 2' THEN
SET price = (SELECT Price
FROM V2Table　WHERE Pid = GetPrice.Pid);
END IF;
END

等同於下面的函數：

CREATE FUNCTION GetPrice (Vendor CHAR(20), PId INT)
RETURNS DECIMAL(10,3)
LANGUAGE SQL
BEGIN
DECLARE price DECIMAL(10,3);
IF Vendor = 'Vendor 1' THEN
SET price = (SELECT ProdPrice
FROM V1Table　WHERE Id = Pid);
ELSE IF Vendor = 'Vendor 2' THEN
SET price = (SELECT Price
FROM V2Table　WHERE Pid = GetPrice.Pid);
END IF;
RETURN price;
END

請注意，盡管使用了 CALL 語句來調用過程，但還需要使用 VALUES 語句從命令行調用函數：

VALUES (GetPrice('IBM', 324))

另一方面，與過程不同的是，您可以在允許表達式的任何上下文中調用函數：

SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 100;
SET price =　GetPrice(Vname, Pid);

因此，正如本節標題所展示的，當您只是從數據庫抽取數據而不執行任何更改時，請考慮使用 SQL 函數而不是使用 SQL 過程。

使用用於臨時數據的臨時表

在 V7 中，DB2 引入了臨時表。對臨時表的操作通常比對常規表的操作快。讓我們看一些原因：

首先，臨時表的創建不會涉及向目錄中插入項，並且臨時表的使用也不會涉及對目錄的訪問；因此，不會有目錄爭用問題。

因為臨時表只能由創建它們的應用程序訪問，因此在其操作中不會涉及鎖定問題。

如果指定了 NOT LOGGED 選項，則不對臨時表上的操作記錄日志（當然，這樣就不可能回滾更改）。因此，如果您的存儲過程生成了大量臨時數據，並只打算在數據庫的一個會話中使用它們，那麼請將這些數據存儲進臨時表，這樣可以顯著地改進性能。

在對 SQL 過程中的臨時表進行任何應用之前，表定義在編譯環境中必須是可用的。例如，在下面的 CLP 腳本（該腳本使用“%”作為語句的終結符）中，表定義的唯一目的就是能夠創建 SQL 過程：

CONNECT TO sample %
DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20)) %
CREATE PROCEDURE INSTT(P1 INT, P2 CHAR(20))
BEGIN
INSERT INTO SESSION.TT VALUES(P1, P2);
END %
CONNECT RESET %

在執行了 CONNECT RESET 命令後，臨時表將不復存在。在運行時，應用程序必須確保在執行使用臨時表的首個查詢之前該表是存在的。最後的這個觀察引出了一個我們從未提及的要點：引用臨時表的任何查詢都將被動態地編譯，即使該查詢被寫成靜態的 SQL。跟其它任何動態查詢一樣，在編譯該查詢之後，它將以已編譯的形式保留在包高速緩存中。在下一次執行相同的查詢時，僅當無法在高速緩存發現它時，DB2 才重新編譯它。

如果您打算創建相對較大的臨時表，並對這些表運行幾個查詢，請考慮定義索引並對它們運行 runstats（顯然後者是填充了表後進行的）。 下一節將介紹更多這方面的內容。

有關在 SQL 過程中使用臨時表的最後一個說明是：如果需要根據在同一個過程中創建的臨時表返回結果集，那麼必須在嵌套的復合語句中定義結果集，如下面的示例所示：

CREATE PROCEDURE INSTT2(P1 INT, P2 CHAR(20))
BEGIN
DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20)) %
INSERT INTO SESSION.TT VALUES(P1, P2);
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM SESSION.TT;
END;
END %

必須在嵌套的復合語句中定義結果集的理由是，DECLARE GLOBAL TEMPORARY TABLE 是一個可執行語句，而可執行語句只能在聲明語句（例如 DECLARE CURSOR）之後編寫。如果我們在游標定義之後在外部作用域中聲明表，那麼當編譯 DECLARE CURSOR 語句時，該表在編譯環境中將不可用，因此編譯會失敗。

保持 DB2 優化器處於被通知狀態

當創建了一個過程時，其單獨的 SQL 查詢被編譯成包中的節。其中，DB2 優化器根據表的統計信息（例如，表大小或某列中數據值出現的相對頻率）以及編譯查詢時可用的索引來選擇查詢的執行方案。當表經過了重大更改時，讓 DB2 再次收集有關這些表的統計信息可能是個好主意。當更新了統計信息時，或者當創建了新的索引時，重新綁定那些與使用表的 SQL 過程相關聯的包，以使 DB2 創建使用最新統計信息和索引的方案，這可能也是一個好主意。

可以使用 RUNSTATS 命令更新表的統計信息。要重新綁定與 SQL 過程關聯的包，可以使用 REBIND_ROUTINE_PACKAGE 內置過程（在 DB2 V8 中可用）。例如，可以使用下面這條命令來重新綁定過程 MYSCHEMA.MYPROC 的包：

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'MYSCHEMA.MYPROC', 'ANY')

其中 'P' 表明該包對應於一個過程，而 'ANY' 表明 SQL 路徑中的任何函數和類型都被當作函數和類型解析。

結束語

在本文中，我提供了一類可能有助於改進 SQL 過程的性能的提示和技巧（請查閱 Yip等編寫的書籍，以獲取 SQL PL 的良好簡介）。作為一般規則，首先考慮系統性能（硬件和 OS）和數據庫管理器（緩沖池、容器和表空間等等）這些基本的問題。DB2 配置顧問程序對於後者非常有幫助。然後請確保應用程序中一些關鍵查詢的方案是合適的。最後，利用本文提供的建議來研究改進您的存儲過程和應用程序。祝您在調優的工作中好運！