在oracle資料庫對於一條SQL，可能存在硬解析、軟解析以及軟軟解析。
硬解析(Hard Parse)是指Oracle在執行目標SQL時，在庫快取找不到可以重用的解析樹和執行計劃，而不得不從頭開始解析SQL並生成相應的Parent Cursor和Child Cursor的過程。硬解析不僅僅耗費CPU等硬體資源，更重要的是其會導致閂(Shared Pool Latch以及Library Cache Latch)等爭用。大量的硬解析會嚴重影響系統的效能及其擴充套件性，尤其對於OLTP系統而言。
那麼怎樣才能降低OLTP應用系統硬解析的數量呢？-----繫結變數。繫結變數的實質就是用一種特殊型別的變數後者稱之為佔位符替代SQL語句中的動態部分，從而保證每次提交的語句都一樣。Oracle資料庫中繫結變數的語法規則為：":variable_name"。下面透過具體實驗來說明繫結變數的具體用法。
1.簡單SELECT語句使用繫結變數
執行不含任何繫結變數的SQL語句如下：

點選(此處)摺疊或開啟

select /*no_bind*/ * from emp where empno=7902;
...省略輸出內容
//檢視執行計劃
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 8qng5nrymtdc1, child number 0
-------------------------------------
select /*no_bind*/ * from emp where empno=7902
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7902)

select /*no_bind*/ * from emp where empno=7876;
1. ...省略輸出內容
3. //檢視執行計劃
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0x1jqmhdwjg7p, child number 0
-------------------------------------
select /*no_bind*/ * from emp where empno=7876
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7876)

查詢v$sqlarea檢視：

點選(此處)摺疊或開啟

SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%no_bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select /*no_bind*/ * from emp where empno=7902 8qng5nrymtdc1 1 1
select /*no_bind*/ * from emp where empno=7876 0x1jqmhdwjg7p 1 1

兩條語句唯一的區別在於WHERE條件不同，且可以看到兩條語句的執行計劃都是索引範圍掃描。僅僅由於SQL文字的細微差別，導致資料庫不得不進行兩次硬解析，佔用了系統的寶貴資源。
我們嘗試用繫結變數重複上面的工作，看一下硬解析的次數以及效果。

點選(此處)摺疊或開啟

var x number;
exec :x := 7902;
select /*bind*/ * from emp where empno=:x;
...省略輸出內容
exec :x :=7876;
select /*bind*/ * from emp where empno=:x;
...省略輸出內容
SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select /*bind*/ * from emp where empno=:x 5cnzfrvqdqccg 1 2
SELECT t.SQL_TEXT,t.SQL_ID,t.CHILD_NUMBER,t.EXECUTIONS FROM v$sql t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS
------------------------------------------------------------ ------------- ------------ ----------
select /*bind*/ * from emp where empno=:x 5cnzfrvqdqccg 0 2

VERSION_COUNT=1,說明兩條語句只進行了一次硬解析（從v$sql輸出結果也印證了這一點），並執行了EXECUTIONS=2次。相比前面未使用繫結變數的情況，使用繫結變數減少了硬解析的次數。

2.儲存過程使用繫結變數
分別建立兩個PROCEDURE，兩個儲存過程的作用一樣都是向表插入10萬條資料，唯一不同的是其中PROC1未使用變數，PROC2使用繫結變數。

點選(此處)摺疊或開啟

CREATE TABLE T (A INT);
//未使用繫結變數
CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
FOR i IN 1 .. 100000
LOOP
EXECUTE IMMEDIATE 'INSERT INTO T VALUES('||i|| ')' ;
END LOOP ;
END ;
//使用繫結變數
CREATE OR REPLACE PROCEDURE PROC2
AS
BEGIN
FOR i IN 1 .. 100000
LOOP
EXECUTE IMMEDIATE 'INSERT INTO T VALUES(:X)' USING i;
END LOOP ;
END ;

下面具體看一下硬解析次數以及執行時間:
proc1的執行情況如下：

點選(此處)摺疊或開啟

select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 505
parse count (hard) 312
parse count (failures) 4
parse count (describe) 0
exec proc1;
Elapsed: 00:01:23.09
select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 100515
parse count (hard) 100313
parse count (failures) 4
parse count (describe) 0

執行前後硬解析次數增加了10萬次左右(100313-312=100001),每一次insert 都進行一次硬解析。同時注意到執行時間為1分鐘23秒。
proc2的執行情況：

點選(此處)摺疊或開啟

select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 100570
parse count (hard) 100345
parse count (failures) 4
parse count (describe) 0
Elapsed: 00:00:00.01
SQL> exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.02
select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 100573
parse count (hard) 100347
parse count (failures) 4
parse count (describe) 0

執行前後硬解析次數僅增加2次(100347-100345)，執行時間僅用了11秒。
看到PROC1的執行時間幾乎是PROC2執行時間的8倍。

3.JAVA語言使用繫結變數的情況
java PrepareStatement物件，可以將sql語句做預編譯操作，被封裝的sql語句可以包含動態引數，減少編譯的次數，提高資料庫效能.PrepareStatement的具體使用方法如下：

點選(此處)摺疊或開啟

for (int i =1 ;i<=1000;i++ ) {
v_sql ="select object_name from objects where object_id= :x ";
stmt=conn.prepareStatement(v_sql);
stmt.setString(1,Integer.toString(i));
rset = stmt.executeQuery();
stmt.close();
}

【總結】
TOM曾說過：Oracle將已解析、已編譯的SQL連同其他內容儲存在共享池(shared pool)中，這個是系統全域性區(System Golbal Area,SGA)中一個非常重要的共享記憶體結構。如果你確實想讓Oracle緩慢執行，甚至幾近停頓，只要根本不使用繫結變數就可以辦到，足以見繫結變數的重要性。

繫結變數之基本概念

相關文章