程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> SQL過程的性能:提示和技巧

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

編輯:DB2教程

簡介

有關如何調優數據庫系統和應用程序的可用建議來源有很多。諸如 OLTP 應用程序的 DB2 調優技巧(以前在 IBM® DB2® 開發者園地上發表)之類的文章通過使用事務和數據並行性以及分析查詢方案,給出了從表空間和索引設計到緩沖池的內存分配等方面的建議。這些方面的內容是性能調優的基礎知識。

但是,有關如何組織存儲過程自身中的邏輯並著眼於其性能的專門建議卻並不多見。本文就提供了這樣一種建議。盡管本文著重於介紹 SQL 過程,但是這裡所提供的大多數信息同樣適用於用其它語言編寫的在應用程序中或存儲過程中嵌入的 SQL 邏輯。

背景知識和術語

在深入研究詳細問題之前,讓我們先回顧 DB2 中有關過程化 SQL 的一些基本術語和概念。過程化 SQL 構造(例如標量變量、IF 語句和 WHILE 循環)是在 DB2 Universal Database™ (UDB) V7 發行版中引入 DB2 的。以前的 DB2 發行版支持 C 和 Java™ 作為存儲過程的語言。V7 引入了 SQL 存儲過程,以及其它許多可以促進 OLTP 應用程序開發的特性(例如臨時表、應用程序保存點和標識列)。

當創建 SQL 過程時,DB2 將過程主體中的 SQL 查詢與過程邏輯區分開來。為了使性能最優,SQL 查詢被靜態地編譯成包中的節。(對於靜態編譯的查詢而言,節主要是由 DB2 優化器為該查詢選擇的存取方案構成的。包是節的集合。有關包和節的更多信息,請參閱 DB2 SQL 參考大全,第 1 卷。)另一方面,過程邏輯被編譯成 DLL(動態鏈接庫)。

在過程的執行期間,每當控制從過程邏輯流向 SQL 語句時,在 DLL 和 DB2 引擎之間就存在“上下文切換”。(在 DB2 V8 中,SQL 過程是在“不受保護的方式”下運行的,即與 DB2 引擎在相同的尋址空間中。因此我們這裡談及的上下文切換並不是操作系統級別上的完全的上下文切換,而是指 DB2 中層的更換。)減少頻繁調用的過程(例如 OLTP 應用程序中的過程)或者處理大量行的過程(例如執行數據清理的過程)中的上下文切換次數,對它們的性能有顯著的影響。本文中的幾個技巧恰好旨在減少這些上下文切換。

剛開始的時候(DB2 通用數據庫 V7 GA),只允許在 SQL 過程中使用 SQL 過程語言(通常稱為 SQL PL)。後來(在 DB2 UDB V7.2 中),在 SQL 函數和觸發器主體中開始支持該語言的子集。SQL PL 的這個子集即所謂的 內聯(inline)SQL PL。“內聯”一詞突出顯示了它與完整語言的重要區別。SQL PL 過程是通過將其單獨的 SQL 查詢靜態地編譯成包中的節實現的,而內聯 SQL PL 函數就象其名稱所展示的,是通過將函數主體內聯到使用它的查詢中實現的。稍後我們將再看一下內聯 SQL PL 及其用法的一些示例。

現在,讓我們研究在使用 SQL 過程語言時可用來提高性能的一些具體工作。

在只使用一條語句即可做到時避免使用多條語句

讓我們從一個簡單的編碼技巧開始。如下所示的單個 INSERT 行序列:

INSERT INTO tab_comp VALUES (item1, price1, qty1);
INSERT INTO tab_comp VALUES (item2, price2, qty2);
INSERT INTO tab_comp VALUES (item3, price3, qty3);

可以改寫成:

INSERT INTO tab_comp VALUES  (item1, price1, qty1),
(item2, price2, qty2),
(item3, price3, qty3);

執行這個多行 INSERT 語句所需時間大約是執行原來三條語句的三分之一。孤立地看,這一改進看起來似乎是微乎其微的,但是,如果這一代碼段是重復執行的(例如該代碼段位於循環體或觸發器體中),那麼改進是非常顯著的。

類似地,如下所示的 SET 語句序列:

SET A = expr1;
SET B = expr2;
SET C = expr3;

可以寫成一條 VALUES 語句:

VALUES expr1, expr2, expr3 INTO A, B, C;

如果任何兩條語句之間都沒有相關性,那麼這一轉換保留了原始序列的語義。為了說明這一點,請考慮:

SET A = monthly_avg * 12;
SET B = (A / 2) * correction_factor;

將上面兩條語句轉換成:

VALUES (monthly_avg * 12, (A / 2) * correction_factor) INTO A, B;

不會保留原始的語義,因為是以“並行”方式對 INTO 關鍵字之前的表達式進行求值的。這意味著賦給 B 的值並不以賦給 A 的值為基礎,這是原始語句預期的語義。

從多個 SQL 語句到一個 SQL 表達式

跟其它編程語言一樣,SQL 語言提供了兩類條件構造:過程型(IF 和 CASE 語句)和函數型(CASE 表達式)。在大多數環境中,可使用任何一種構造來表達計算,到底使用哪一種只是喜好問題。但是,使用 CASE 表達式編寫的邏輯不但比使用 CASE 或 IF 語句編寫的邏輯更緊湊,而且更有效。

請考慮下面的 SQL PL 代碼片段:

IF (Price <= MaxPrice) THEN
INSERT INTO tab_comp(Id, Val) VALUES(Oid, Price);
ELSE
INSERT INTO tab_comp(Id, Val) VALUES(Oid, MaxPrice);
END IF;

IF 子句中的條件僅用於決定將什麼值插入 tab_comp.Val 列中。為了避免過程層和數據流層之間的上下文切換,可利用 CASE 表達式將相同的邏輯表示成一個 INSERT 語句:

INSERT INTO tab_comp(Id, Val)
VALUES(Oid,
CASE
WHEN (Price <= MaxPrice) THEN Price
ELSE MaxPrice
END);

值得注意的是,CASE 表達式可在任何希望有標量值的上下文中使用。特別地,可在賦值符號的右邊使用它們。例如:

IF (Name IS NOT NULL) THEN
SET ProdName = Name;
ELSEIF (NameStr IS NOT NULL) THEN
SET ProdName = NameStr;
ELSE
SET ProdName = DefaultName;
END IF;

可以改寫成:

SET ProdName = (CASE
WHEN (Name IS NOT NULL) THEN Name
WHEN (NameStr IS NOT NULL) THEN NameStr
ELSE DefaultName
END);

實際上,這個特殊的示例有一個更好的解決方案:

SET ProdName = COALESCE(Name, NameStr, DefaultName);

使用 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 配置顧問程序對於後者非常有幫助。然後請確保應用程序中一些關鍵查詢的方案是合適的。最後,利用本文提供的建議來研究改進您的存儲過程和應用程序。祝您在調優的工作中好運!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved