【DB筆試面試581】在Oracle中，綁定變數是什麼？綁定變數有什麼優缺點？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，綁定變數是什麼？綁定變數有什麼優缺點？

♣

答案部分

綁定變數這節的內容較多，下面給出這節涉及到的關係圖：

（一）綁定變數的含義及優缺點

通常在高並發的OLTP系統中，可能會出現這樣的現象，單個SQL的寫法、執行計劃、性能都是沒問題的，但整個系統的性能就是很差，這表現在當系統並發的數量增加時，整個系統負載很高，CPU佔用率接近100%。其實，這種系統性能隨著並發量的遞增而顯著降低的現象，往往是因為這些系統沒有使用綁定變數而產生了大量的硬解析所致。因為同一條SQL語句僅僅由於謂詞部分變數的不同而在執行的時候就需要重新進行一次硬解析，造成SQL執行計劃不能共享，這極大地耗費了系統時間和系統CPU資源。那麼怎樣才能降低OLTP應用系統的硬解析的數量呢？答案就是使用綁定變數。高並發的OLTP系統若沒有使用綁定變數則會導致硬解析很大，這在AWR中的Load Profile部分可以很容易的看出來。

使用綁定變數能夠有效降低系統硬解析的數量。對於同一類型的SQL語句若使用了綁定變數，則SQL文本就變得完全相同了，據此計算出來的哈希值也就完全相同，這就具備了可以重用解析樹和執行計劃的基礎條件。這裡的同一類型的SQL語句指的是除SQL文本中對應的輸入值不同外其它部分都一模一樣的SQL語句。例如，銀行的查詢餘額的SQL語句，在成千上萬次查詢中都只是賬戶名不同，而SQL語句的其它部分都一樣。若沒有使用綁定變數，則每查詢一次都必須進行一次硬解析。如果使用了綁定變數，假設每次可以節省0.001秒，那麼在高並發下上千萬次查詢節省下來的時間將是非常大的，這在無形中就提高了系統的響應時間。

綁定變數（Bind Variable）其實質是變數，類似於經常使用的替代變數，只不過替代變數使用「&」作為佔位符，而綁定變數使用英文冒號（:）作為佔位符，替代變數使用方式為&VARIABLE_PARA，相應的綁定變數則為:BIND_VARIABLE_PARA。綁定變數通常出現在SQL文本中，用於替換WHERE或VALUES子句中的具體值。

綁定變數的優點如下所示：

①　可以在庫快取（Library Cache）中共享游標，避免硬解析以及與之相關的額外開銷。換句話說，綁定變數可以有效地減少SQL硬解析的次數，從而減少系統資源開銷，這也是使用綁定變數最大的作用。

②　在大批量數據操作時，可以大量減少閂鎖的使用，從而避免閂鎖（Latch）的爭用。

③　提高了程式碼的可讀性（避免拼接式的硬編碼）和安全性（防止SQL注入）。

綁定變數的缺點主要體現在當使用綁定變數時，查詢優化器會忽略其具體值，因此，其預估的準確性遠不如使用字面量值真實。當表的列上存在數據傾斜（表上的數據非均勻分布）時，Oracle可能會提供錯誤的執行計劃，從而使得非高效的執行計劃被使用。

需要注意的是，目標SQL中的綁定變數個數不宜太多，否則可能會導致目標SQL總的執行時間大幅度增長。增長的時間主要耗費在執行目標SQL時對每一個綁定變數都用其實際的值來替換（這個過程就是所謂的綁定變數值替換），目標SQL的SQL文本中的綁定變數的個數越多，這個替換過程所耗費的時間就越長，該SQL總的執行時間也就越長。

（二）綁定變數的適用場合

對於綁定變數應該根據系統的類型來決定是否使用綁定變數，如下所示：

l 在高並發的OLTP系統中，SQL語句重複執行頻度高，但處理的數據量較少，結果集也相對較小，尤其是使用表上的索引來縮小中間結果集，其解析時間通常會接近或高於執行時間，因此，在該場合一定要使用綁定變數，並且最好是使用批量綁定，因為可以有效降低系統硬解析的數量，這也是OLTP類型的系統在資料庫端具備良好的性能和可擴展性的前提條件。

l 在OLAP/DSS系統中，SQL語句執行次數相對較少，但返回的數據量較大，其SQL語句執行時間遠高於其解析時間，硬解析對系統性能的影響是微乎其微的，因此，使用綁定變數對於總的執行時間影響不大，對系統性能的提升也非常有限。

l 對於OLAP和OLTP混合型的應用系統，如果有循環，不管這個循環是在前台程式碼還是在後台PL/SQL程式碼中，循環內部的SQL語句一定要使用綁定變數，並且最好是使用批量綁定：至於循環外部的SQL語句，可以不使用綁定變數。

需要注意的是，對於實際的資料庫對象，例如表、視圖等，不能使用綁定變數替換，只能替換字面量。如果對象名是在運行時生成的，那麼需要對其用字元串拼接，同時，SQL只會匹配已經在共享池中存在且相同的對象名。

（三）綁定變數的使用方法

--①　在SQL中，用法如下所示：  var v_empno number; --聲明變數  exec :v_empno :=7369; --變數賦值  select * from scott.emp where empno=:v_empno;--使用綁定變數    --②　在PL/SQL中，有如下幾種用法：  -----a.在靜態SQL中使用綁定變數：  DECLARE    V_NAME VARCHAR2(10);  BEGIN    EXECUTE IMMEDIATE 'select ename from scott.emp where empno=:1' INTO V_NAME USING 7369;    DBMS_OUTPUT.PUT_LINE(V_NAME);  END;  -----b.在動態SQL中使用綁定變數：  DROP TABLE T_EMP_LHR;  CREATE TABLE T_EMP_LHR AS SELECT * FROM SCOTT.EMP;  DECLARE    V_SQL1 VARCHAR2(4000);    V_SQL2 VARCHAR2(4000);      V_TMP1 NUMBER;      V_TMP2 NUMBER;  BEGIN    V_SQL1:='INSERT INTO T_EMP_LHR(empno,ename,job) values(:1,:2,:3)';    EXECUTE IMMEDIATE V_SQL1 USING 6666,'lhr6','DBA';      V_TMP1:=SQL%ROWCOUNT;    V_SQL2:='INSERT INTO T_EMP_LHR(empno,ename,job) values(:1,:1,:1)';    EXECUTE IMMEDIATE V_SQL2 USING 6667,'lhr7','DBA';      V_TMP2:=SQL%ROWCOUNT;    DBMS_OUTPUT.PUT_LINE(V_TMP1+V_TMP2);  END;    

對於上述這種使用綁定變數的方式，關鍵字「USING」後傳入的綁定變數具體輸入值只與對應綁定變數在目標SQL中所處的位置有關，而與其名稱無關，這意味著只要目標SQL中綁定變數所處的位置不同，它們所對應的綁定變數名稱是可以相同的。上述程式碼執行結果為2，查詢：

LHR@orclasm > SELECT * FROM T_EMP_LHR t WHERE t.job='DBA';         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------        6666 lhr6       DBA        6667 lhr7       DBA    --再給出一個示例：  DECLARE    V_COLUNMS VARCHAR2(30) :='EMPNO';    V_SQL VARCHAR2(4000);    V_ENAME VARCHAR2(20);  BEGIN    V_SQL:='DELETE FROM T_EMP_LHR WHERE '||V_COLUNMS||'=:1 RETURNING ENAME INTO :2';    EXECUTE IMMEDIATE V_SQL USING 7369  RETURNING INTO  V_ENAME;    DBMS_OUTPUT.PUT_LINE(V_ENAME);  END;  

上述PL/SQL程式碼實現的是，刪除表T_EMP_LHR里列EMPNO的值為7369的記錄，並且將該記錄的列ENAME的值列印出來。

c. 在PL/SQL中通過批量綁定的方式使用綁定變數。

PL/SQL中的「批量綁定」是一種優化後的使用綁定變數的方式。批量綁定的優勢在於它是一次處理一批數據，而不是像常規方式那樣一次只處理一條數據，所以它能夠有效減少PL/SQL引擎和SQL引擎上下文切換的次數。批量綁定的主要方式是使用BULK COLLECT INTO和FORALL的方式來實現，下面給出一個示例：

DECLARE    TYPE EMPCURTYPE IS REF CURSOR; --定義游標類型及游標變數    EMP_CV EMPCURTYPE;    TYPE ENAME_TABLE_TYPE IS TABLE OF T_EMP_LHR.ENAME%TYPE INDEX BY BINARY_INTEGER; --定義結果集類型及變數    ENAME_TABLE ENAME_TABLE_TYPE;    SQL_STAT    VARCHAR2(120);    CN_BATCH_SIZE CONSTANT PLS_INTEGER := 1000;  BEGIN    SQL_STAT := 'SELECT ENAME FROM T_EMP_LHR WHERE DEPTNO > :1'; --動態SQL字元串    OPEN EMP_CV FOR SQL_STAT  USING 1; --從動態SQL中打開游標    LOOP      FETCH EMP_CV BULK COLLECT INTO ENAME_TABLE LIMIT CN_BATCH_SIZE; --使用BULK COLLECT INTO提取結果集      FOR I IN 1 .. ENAME_TABLE.COUNT LOOP        DBMS_OUTPUT.PUT_LINE('Employee Name is ' || ENAME_TABLE(I));      END LOOP;      EXIT WHEN ENAME_TABLE.COUNT < CN_BATCH_SIZE;    END LOOP;    CLOSE EMP_CV;  END;  

其它示例可以參考【3.1.10.2 如何使用批量動態SQL（FORALL及BULK子句的使用）？】。

③　在Java中使用綁定變數

在Java中也有綁定變數和批量綁定的用法，本書不再詳解。

真題1、下面有關SQL綁定變數的描述中，錯誤的是（）

A、綁定變數是指在SQL語句中使用變數，改變變數的值來改變SQL語句的執行結果

B、使用綁定變數，可以減少SQL語句的解析，能減少資料庫引擎消耗在SQL語句解析上的資源

C、使用綁定變數，提高了編程效率和可靠性，減少訪問資料庫的次數

D、使用綁定變數，查詢優化器會預估的比字面變數更加真實

答案：D。

綁定變數是相對文本變數來講的，所謂文本變數是指在SQL中直接書寫查詢條件，這樣的SQL在不同條件下需要反覆解析，綁定變數是指使用變數來代替直接書寫條件，查詢綁定變數在運行時傳遞，然後綁定執行。優點是減少硬解析，降低CPU的爭用，節省Shared Pool；缺點是不能使用固定的執行計劃，SQL優化比較困難。

本題中，對於選項A，綁定變數就是之前不知道具體的值，只有運行的時候才知道值，改變變數的值來改變SQL語句的執行結果。所以，選項A錯誤。

對於選項B，使用綁定變數，可以減少SQL語句的解析，說法正確。所以，選項B錯誤。

對於選項C，使用綁定變數，減少解析次數，提高了編程效率和可靠性。所以，選項C錯誤。

對於選項D，使用綁定變數，查詢優化器不知道具體的值，所以，其執行計劃也不真實。所以，選項D正確。

所以，本題的答案為D。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。