JAVA 程式中使用ORACLE 綁定變量( bind variable )

1、為什麼要使用綁定變量 

      （1）SQL語 句硬分析(Hard Parse)太多，嚴重消耗CPU資源，延長了SQL語句總的執行時間 

        SQL語句的執行過程分幾個步驟：文法檢查、分析、執行、 傳回結果。其中分析分為硬分析(Hard Parse)和軟分析(Soft Parse)。一條SQL語句通過文法檢查後，Oracle 會先去shared pool 中找是否有相同的sql，如果找着了，就叫軟分析，然後執行SQL語句。硬分析主要是檢查該sql所涉及到的所有對象是否有效以及權限等關系，然後根據RBO或CBO模 式生成執行計劃，然後才執行SQL語 句。 

   可以看出，硬分析比軟分析多了很多動作， 而這裡面的關鍵是“在shared pool 中是否有相同的sql”，而這就取決于是否使用綁定變量。 

      另：oracle9i引入了soft soft parse，先到pga中的session cursor cache list清單中去查找（session cursor cache list的長度是由session_cache_cursor參數決定的），如果沒有找到這條sql，這時候才去檢查shard_pool.   對于Oltp系統，很多時候硬分析的代價比執行還要高，這個我們可以通過10046事件跟蹤得知。 

    （2）共享池中SQL語 句數量太多，重用性極低，加速了SQL語 句的老化，導緻共享池碎片過多。 

      共享池中不同的SQL語句數量巨大，根據LRU原則，一些語句逐漸老化，最終被清理出共享池；這樣就導緻shared_pool_size 裡面命中率下降，共享池碎片增多，可用記憶體空間不足。而為了維護共享池内部結構，需要 使用latch，一種内部生命周期很短的lock，這将使用大量的cpu 資源，使得性能急劇下降。 

      不使用綁定變量違背了oracle 的shared pool 的設計的原則，違背了這個設計用來共享的思想。 

2、怎麼檢視沒有使用綁定變量 

select * from v$sql or v$sqlarea 檢視是否有很多類似的語句，除 了變量不一樣，其他的都一樣 

3、如何使用綁定變量？ 

編 寫java 程式時，我們習慣都是定義JAVA 的程式變量，放入SQL 語句中，如 

String v_id = 'xxxxx'; 

String v_sql = 'select name from table_a where id = ' + v_id ; 

以 上代碼，看起來是使用了變量v_id ，但這卻是java 的程式變量，而不是oracle 的綁定變量，語句傳遞到資料庫後，此java 的程式變量 

已經被替換成具 體的常量值，變成： 

select * from table_a where name = 'xxxxx' ; 

假 定這個語句第一次執行，會進行硬分析。後來，同一段java 代碼中v_id 值發現變化（v_id = 'yyyyyy'），資料庫又接收到這樣的語句： 

select * from table_a where name = 'yyyyyy' ; 

ORACLE 并不認為以上兩條語句是相同的語句，是以對第二條語句會又做一次硬分析。這兩條語句的執 行計劃可是一樣的！ 

其 實，隻需将以上java 代碼改成以下這樣，就使用了oracle 的綁定變量： 

String v_id = 'xxxxx'; 

String v_sql = 'select name from table_a where id = ? '; //嵌入綁定變量 

stmt = con.prepareStatement( v_sql ); 

stmt.setString(1, v_id ); //為綁定變量指派 

stmt.executeQuery(); 

在Java中，結合使用setXXX 系列方法，可以為不同資料類型的綁定變量進行指派，進而大大優化了SQL 語句的性能。 

4、java中 應用綁定變量的例子 

PreparedStatement stmt = conn.prepareStatement('select a from b where c = ? '); 

stmt.setLong(1,123); 

stmt.executeQuery() 

…… 

結論： 

    綁定變量主要适用在Oltp，運作時間很短的系統。如客服系統，時時地進行insert方面的系統。 數 據倉庫系統不适用，和資料庫倉庫系統的一條sql運 行時間相比，硬分析的代價顯然是微不足道的，通過硬分析去選擇正确的執行計劃才是關鍵。 

  簡 單一句話，在Oltp系統中應用綁定變量，性能會有質的提高