近期查看公司業(yè)務(wù)系統(tǒng)的DB層代碼，發(fā)現(xiàn)SQL語句多采用常量形式，難怪公司總抱怨系統(tǒng)效率低。我知道采用綁定變量可以很大程度上提高效率，但卻解釋不清，到網(wǎng)上百度了一下，找到這篇文章，寫的還不錯，就摘錄下來。以備以后參考。

*******************************************************************************

     在JAVA 源程序中編寫SQL語句時使用ORACLE 綁定變量( bind variable )
在JAVA中的SQL 語句的編寫方面，沒有使用ORACLE 綁定變量，很大程度上降低了數(shù)據(jù)庫的性能，表現(xiàn)在兩個方面：
    1、SQL語句硬分析(Hard Parse)太多，嚴(yán)重消耗CPU資源，延長了SQL語句總的執(zhí)行時間SQL語句的執(zhí)行過程分幾個步驟：語法檢查、分析、執(zhí)行、返回結(jié)果。其中分析又分為硬分析(Hard Parse)和軟分析(Soft Parse)。

一條SQL語句通過語法檢查后，Oracle 會先去shared pool 中找是否有相同的sql，如果找著了，就叫軟分析，然后執(zhí)行SQL語句。

硬分析主要是檢查該sql所涉及到的所有對象是否有效以及權(quán)限等關(guān)系，然后根據(jù)RBO或CBO模式生成執(zhí)行計劃，然后才執(zhí)行SQL語句。

可以看出，硬分析比軟分析多了很多動作，而這里面的關(guān)鍵是“在shared pool 中是否有相同的sql”，而這就取決于是否使用綁定變量。
    2、共享池中SQL語句數(shù)量太多，重用性極低，加速了SQL語句的老化，導(dǎo)致共享池碎片過多。

共享池中不同的SQL語句數(shù)量巨大，根據(jù)LRU原則，一些語句逐漸老化，最終被清理出共享池；這樣就導(dǎo)致shared_pool_size 里面命中率下降，共享池碎片增多，可用內(nèi)存空間不足。而為了維護共享池內(nèi)部結(jié)構(gòu)，需要使用latch，一種內(nèi)部生命周期很短的lock，這將使用大量的cpu 資源，使得性能急劇下降。不使用綁定變量違背了oracle 的shared pool 的設(shè)計的原則，違背了這個設(shè)計用來共享的思想。

編寫java 程序時，我們習(xí)慣都是定義JAVA 的程序變量，放入SQL 語句中，如
String v_id = 'xxxxx';

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

以上代碼，看起來是使用了變量v_id ，但這卻是java 的程序變量，而不是oracle 的綁定變量，語句傳遞到數(shù)據(jù)庫后，此java 的程序變量已經(jīng)被替換成具體的常量值，變成：
select * from table_a where name = 'xxxxx' ;
假定這個語句第一次執(zhí)行，會進行硬分析。后來，同一段java 代碼中v_id 值發(fā)現(xiàn)變化（v_id = 'yyyyyy'），數(shù)據(jù)庫又接收到這樣的語句：
select * from table_a where name = 'yyyyyy' ;

ORACLE 并不認(rèn)為以上兩條語句是相同的語句，因此對第二條語句會又做一次硬分析。這兩條語句的執(zhí)行計劃可是一樣的！
其實，只需將以上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中，結(jié)合使用setXXX 系列方法，可以為不同數(shù)據(jù)類型的綁定變量進行賦值，從而大大優(yōu)化了SQL 語句的性能。

如下例子演示了三種查詢所花費的時間：

1.  不使用綁定變量

declare
 type rc is ref cursor;
   l_rc rc;
   l_dummy all_objects.object_name%type;
   l_start number default dbms_utility.get_time;
 begin
 for i in 1 .. 1000
   loop
 open l_rc for
   'select object_name
    from all_objects
    where object_id = ' || i;
 fetch l_rc into l_dummy;
 close l_rc;
 end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
    ' seconds...' );
 end;
輸出：8.25 seconds...

 
2.  增加Rule提示
declare
 type rc is ref cursor;
   l_rc rc;
   l_dummy all_objects.object_name%type;
   l_start number default dbms_utility.get_time;
 begin
 for i in 1 .. 1000
   loop
 open l_rc for
 'select /*+ rule */ object_name
    from all_objects
   where object_id = ' || i;
 fetch l_rc into l_dummy;
 close l_rc;
 end loop;
 dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
 end;
輸出：8.23 seconds...
3.  使用綁定變量
declare
 type rc is ref cursor;
   l_rc rc;
   l_dummy all_objects.object_name%type;
   l_start number default dbms_utility.get_time;
 begin
 for i in 1 .. 1000
 loop
 open l_rc for
     'select object_name
     from all_objects
     where object_id = :x'
 using i;
 fetch l_rc into l_dummy;
 close l_rc;
 end loop;
 dbms_output.put_line
   ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
    ' seconds...' );
 end;
輸出：.56 seconds...

另外：某些時候oracle使用綁定變量性能反而更差，此部分有待繼續(xù)找資料。

其中對于隔相當(dāng)一段時間才執(zhí)行一次的ｓｑｌ語句，這是利用綁定變量的好處會被不能有效利用優(yōu)化器而抵消。