使用綁定變量

如果我要寫一本書談談如何建構不可擴縮的Oracle應用，肯定會把“不要使用綁定變量”作為第一章和最後一章的标題重點強調。這是導緻性能問題的一個主要原因，也是阻礙可擴縮性的一個重要因素。Oracle将已解析、已編譯的SQL連同其他内容存儲在共享池（shared pool）中，這是系統全局區（System Global Area ，SGA）中一個非常重要的共享記憶體結構。第4章将詳細讨論共享池。這個結構能完成“平滑”操作，但有一個前提，要求開發人員在大多數情況下都會使用綁定變量。如果你确實想讓Oracle緩慢地運作，甚至幾近停頓，隻要根本不使用綁定變量就可以辦到。

綁定變量（bind variable）是查詢中的一個占位符。例如，要擷取員工123的相應記錄，可以使用以下查詢：

select * from emp where empno = 123;

或者，也可以将綁定變量:empno設定為123，并執行以下查詢：

select * from emp where empno = :empno;

在典型的系統中，你可能隻查詢一次員工123，然後不再查詢這個員工。之後，你可能會查詢員工456，然後是員工789，如此等等。如果在查詢中使用直接量（常量），那麼每個查詢都将是一個全新的查詢，在資料庫看來以前從未見過，必須對查詢進行解析、限定（命名解析）、安全性檢查、優化等。簡單地講，就是你執行的每條不同的語句都要在執行時進行編譯。

第二個查詢使用了一個綁定變量:empno，變量值在查詢執行時提供。這個查詢隻編譯一次，随後會把查詢計劃存儲在一個共享池（庫緩存）中，以便以後擷取和重用這個查詢計劃。以上兩個查詢在性能和可擴縮性方面有很大差别，甚至可以說有天壤之别。

62 / 860

從前面的描述應該能清楚地看到，與重用已解析的查詢計劃（稱為軟解析，soft parse）相比，解析包含有寫死變量的語句（稱為硬解析，hard parse）需要的時間更長，而且要消耗更多的資源。硬解析會減少系統能支援的使用者數，但程度如何不太明顯。這部分取決于多耗費了多少資源，但更重要的因素是庫緩存所用的闩定（latching）機制。硬解析一個查詢時，資料庫會更長時間地占用一種低級串行化裝置，這稱為闩（latch），有關的詳細内容請參見第6章。這些闩能保護Oracle共享記憶體中的資料結構不會同時被兩個程序修改（否則，Oracle最 後會得到遭到破壞的資料結構），而且如果有人正在修改資料結構，則不允許另外的人再來讀取。對這些資料結構加闩的時間越長、越頻繁，排隊等待闩的程序就越 多，等待隊列也越長。你可能開始獨占珍貴的資源。有時你的計算機顯然利用不足，但是資料庫中的所有應用都運作得非常慢。造成這種現象的原因可能是有人占據 着某種串行化裝置，而其他等待串行化裝置的人開始排隊，是以你無法全速運作。資料庫中隻要有一個應用表現不佳，就會嚴重地影響所有其他應用的性能。如果隻 有一個小應用沒有使用綁定變量，那麼即使其他應用原本設計得很好，能适當地将已解析的SQL放在共享池中以備重用，但因為這個小應用的存在，過一段時間就會從共享池中删除已存儲的SQL。這就使得這些設計得當的應用也必須再次硬解析SQL。真是一粒老鼠屎就能毀了一鍋湯。

如果使用綁定變量，無論是誰，隻要送出引用同一對象的同一個查詢，都會使用共享池中已編譯的查詢計劃。這樣你的子例程隻編譯一次就可以反複使用。這樣做效率很高，這也正是資料庫期望你采用的做法。你使用的資源會更少（軟解析耗費的資源相當少），不僅如此，占用闩的時間也更短，而且不再那麼頻繁地需要闩。這些 都會改善應用的性能和可擴縮性。

要想知道使用綁定變量在性能方面會帶來多大的差别，隻需要運作一個非常小的測試來看看。在這個測試中，将在一個表中插入一些記錄行。我使用如下所示的一個簡單的表：

[email protected]> drop table t;

Table dropped.

[email protected]> create table t ( x int );

Table created.

下面再建立兩個非常簡單的存儲過程。它們都向這個表中插入數字1到10 000；不過，第一個過程使用了一條帶綁定變量的SQL語句：

[email protected]> create or replace procedure proc1

2 as

3 begin

4 for i in 1 .. 10000

5 loop

6 execute immediate

7 'insert into t values ( :x )' using i;

63 / 860

8 end loop;

9 end;

10 /

Procedure created.

第二個過程則分别為要插入的每一行構造一條獨特的SQL語句：

[email protected]> create or replace procedure proc2

2 as

3 begin

4 for i in 1 .. 10000

5 loop

6 execute immediate

7 'insert into t values ( '||i||')';

8 end loop;

9 end;

10 /

Procedure created.

現在看來，二者之間惟一的差别，是一個過程使用了綁定變量，而另一個沒有使用。它們都使用了動态SQL（所謂動态SQL是指直到運作時才确定的SQL），而且過程中的邏輯也是相同的。不同之處隻在于是否使用了綁定變量。

下面用我開發的一個簡單工具runstats對這兩個方法詳細地進行比較：

注意 關于安裝runstats和其他工具的有關細節，請參見本書開頭的“配置環境”一節。

[email protected]> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

[email protected]> exec proc1

PL/SQL procedure successfully completed.

64 / 860

[email protected]> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

[email protected]> exec proc2

PL/SQL procedure successfully completed.

[email protected]> exec runstats_pkg.rs_stop(1000)

Run1 ran in 159 hsecs

Run2 ran in 516 hsecs

run 1 ran in 30.81% of the time

結果清楚地顯示出，從牆上時鐘來看，proc2（沒有使用綁定變量）插入10 000行記錄的時間比proc1（使用了綁定變量）要多出很多。實際上，proc2需要的時間是proc1的3倍多，這說明，在這種情況下，對于每個“無綁定變量”的INSERT，執行語句所需時間中有2/3僅用于解析語句！

注意 如果願意，也可以不用runstats，而是在SQL*Plus中執行指令SET TIMING ON，然後運作proc1和proc2，這樣也能進行比較。

不過，對于proc2，還有更糟糕的呢！runstats工具生成了一個報告，顯示出這兩種方法在闩使用率方面的差别，另外還提供了諸如解析次數之類的統計結果。這裡我要求runstats列印出差距在1 000以上的比較結果（這正是rs_stop調用中1000的含義）。檢視這個資訊時，可以看到各方法使用的資源存在顯著的差别：

Name Run1 Run2 Diff

STAT...parse count (hard) 4 10,003 9,999

LATCH.library cache pin 80,222 110,221 29,999

LATCH.library cache pin alloca 40,161 80,153 39,992

LATCH.row cache enqueue latch 78 40,082 40,004

LATCH.row cache objects 98 40,102 40,004

LATCH.child cursor hash table 35 80,023 79,988

65 / 860

LATCH.shared pool 50,455 162,577 112,122

LATCH.library cache 110,524 250,510 139,986

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

407,973 889,287 481,314 45.88%

PL/SQL procedure successfully completed.

注意 你自己測試時可能會得到稍微不同的值。如果你得到的數值和上面的一樣，特别是如果闩數都與我的測試結果完全相同，那倒是很奇怪。不過，假設你也像我一樣，也是在Linux平台上使用Oracle9i Release 2，應該能看到類似的結果。不論哪個版本，可以想見，硬解析處理每個插入所用的闩數總是要高于軟解析（對于軟解析，更确切的說法應該是，隻解析一次插入，然後反複執行）。還在同一台機器上，但是如果使用 Oracle 10g Release 1執行前面的測試，會得到以下結果：與未使用綁定變量的方法相比，綁定變量方法執行的耗用時間是前者的1/10，而所用的闩總數是前者的17%。這有兩個原因，首先，10g 是一個新的版本，有一些内部算法有所調整；另一個原因是在10g中，PL/SQL采用了一種改進的方法來處理動态SQL。

可以看到，如果使用了綁定變量（後面稱為綁定變量方法），則隻有4次硬解析；沒有使用綁定變量時（後面稱為無綁定變量方法），卻有不下10 000次的硬解析（每次插入都會帶來一次硬解析）。還可以看到，無綁定變量方法所用的闩數是綁定變量方法的兩倍之多。這是因為，要想修改這個共享結構，Oracle必須當心，一次隻能讓一個程序處理（如果兩個程序或線程試圖同時更新同一個記憶體中的資料結構，将非常糟糕，可能會導緻大量破壞）。是以，Oracle采用了一種闩定（latching）機制來完成串行化通路，闩（latch） 是一種輕量級鎖定裝置。不要被“輕量級”這個詞蒙住了，作為一種串行化裝置，闩一次隻允許一個程序短期地通路資料結構。闩往往被硬解析實作濫用，而遺憾的 是，這正是闩最常見的用法之一。共享池的闩和庫緩存的闩就是不折不扣的闩；它們成為人們頻繁争搶的目标。這說明，想要同時硬解析語句的使用者越多，性能問題 就會變得越來越嚴重。人們執行的解析越多，對共享池的闩競争就越厲害，隊列會排得越長，等待的時間也越久。

注意 如果機器的處理器不止一個，在9i 和以上版本中，共享池還可以劃分為多個子池，每個子池都由其自己的闩保護。這樣即使應用沒有使用綁定變量，也可以提高可擴縮性，但是這并沒有從根本上克服闩定問題。

執行無綁定變量的SQL語句，很像是在每個方法調用前都要編譯子例程。假設把Java源代碼傳遞給客戶，在調用類中的方法之前，客戶必須調用Java編譯器，編譯這個類，再運作方法，然後丢掉位元組碼。下一次想要執行同樣的方法時，他們還要把這個過程再來一遍：先編譯，再運作，然後丢掉位元組碼。你肯定不希望在應用中這樣做。資料庫裡也應該一樣，絕對不要這樣做。

對于這個特定的項目，可以把現有的代碼改寫為使用綁定變量，這是最好的做法。改寫後的代碼與原先比起來，速度上有呈數量級的增長，而且系統能支援的并發使用者 數也增加了幾倍。不過，在時間和精力投入方面卻要付出很大的代價。并不是說使用綁定變量有多難，也不是說使用綁定變量容易出錯，而隻是因為開發人員最初沒 有使用綁定變量的意識，是以必須回過頭去，幾乎把所有代碼都檢查和修改一遍。如果他們從第一天起就很清楚在應用中使用綁定變量至關重要，就不用費這麼大的 功夫了。