oracle將已解析、已編譯的SQL連同其他內(nèi)容存儲在共享池(shared pool)中，這是系統(tǒng)全局區(qū)(System Global Area,SGA)中一個非常重要的共享內(nèi)存結(jié)構(gòu).

綁定變量(bind variable)是查詢中的一個占位符。

例如，要獲取員工編號7369的相應(yīng)記錄，可以使用：

scott@ORCL>select?*?from?emp?where?empno=7369;

?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE??????????????SAL???????COMM????DEPTNO
----------?----------?---------?----------?--------------?----------?----------????----------
??????7369?SMITH??????CLERK???????????7902?17-12月-80????????????800????????????????20

或者可以將綁定變量:empno設(shè)置為7369，再執(zhí)行查詢:

scott@ORCL>variable?empno?number;
scott@ORCL>exec?:empno?:=?7369;
PL/SQL?過程已成功完成。

scott@ORCL>select?*?from?emp?where?empno=:empno;

?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE??????????????SAL???????COMM????DEPTNO
----------?----------?---------?----------?--------------?----------?----------????----------
??????7369?SMITH??????CLERK???????????7902?17-12月-80????????????800????????????????20

在典型的系統(tǒng)中，你可能只查詢一次員工?7369，然后不再查詢這個員工了。之后，你可能會查詢員工666，然后是員工888，如此等等。如果在查詢中使用直接量(常量),那么每個查詢都將是一個全新的查詢，在數(shù)據(jù)庫看來以前從未見過，必須對查詢進(jìn)行解析、限定(命名解析)、安全性檢查、優(yōu)化等。你執(zhí)行的每條不同的語句都要在執(zhí)行時進(jìn)行編譯。

第二個查詢使用了一個綁定變量:empno，變量值在查詢執(zhí)行時提供。這個查詢只編譯一次，隨后會把查詢計劃存儲在一個共享池(庫緩存)中，以便以后獲取和重用這個查詢計劃。

--------------------------------------------------------------------------------------------------------------------------------

第一個過程使用了帶綁定變量的sql語句，如下：

scott@ORCL>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;
??8??end?loop;
??9??end;
?10??/

過程已創(chuàng)建。

第二個過程則分別要為插入的每一行構(gòu)造一條獨(dú)特的sql語句，如下：

scott@ORCL>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??/

過程已創(chuàng)建。

比較兩者性能區(qū)別：

scott@ORCL>exec?runstats_pkg.rs_start
PL/SQL?過程已成功完成。

scott@ORCL>exec?proc1
PL/SQL?過程已成功完成。

scott@ORCL>exec?runstats_pkg.rs_middle
PL/SQL?過程已成功完成。

scott@ORCL>exec?proc2
PL/SQL?過程已成功完成。

scott@ORCL>exec?runstats_pkg.rs_stop(1000)?#打印差距大于1000以上的比較結(jié)果
Run1?ran?in?25?cpu?hsecs
Run2?ran?in?396?cpu?hsecs
run?1?ran?in?6.31%?of?the?time

Name??????????????????????????????????Run1????????Run2????????Diff
LATCH.object?queue?header?oper???????2,569?????????280??????-2,289
STAT...sql?area?evicted??????????????????0???????7,506???????7,506
STAT...session?cursor?cache?hi??????10,010??????????63??????-9,947
STAT...calls?to?kcmgcs??????????????????51??????10,048???????9,997
STAT...enqueue?requests?????????????????30??????10,029???????9,999
STAT...enqueue?releases?????????????????28??????10,029??????10,001
STAT...parse?count?(hard)????????????????4??????10,011??????10,007
STAT...parse?count?(total)??????????????26??????10,057??????10,031
STAT...consistent?gets?from?ca??????????51??????10,126??????10,075
STAT...consistent?gets??????????????????96??????10,318??????10,222
STAT...consistent?gets?from?ca??????????96??????10,318??????10,222
STAT...file?io?wait?time????????????19,052???????????0?????-19,052
LATCH.enqueue?hash?chains??????????????853??????20,669??????19,816
LATCH.enqueues?????????????????????????759??????20,626??????19,867
STAT...db?block?gets????????????????10,421??????30,373??????19,952
STAT...db?block?gets?from?cach??????10,421??????30,373??????19,952
STAT...db?block?gets?from?cach??????????61??????20,042??????19,981
STAT...session?logical?reads????????10,517??????40,691??????30,174
STAT...recursive?calls??????????????10,328??????41,019??????30,691
STAT...session?uga?memory?max??????123,512??????72,952?????-50,560
LATCH.kks?stats??????????????????????????9??????50,688??????50,679
LATCH.cache?buffers?chains??????????57,017?????113,844??????56,827
STAT...session?uga?memory???????????65,488?????130,976??????65,488
STAT...session?pga?memory?max??????131,072??????65,536?????-65,536
STAT...session?pga?memory???????????65,536?????131,072??????65,536
LATCH.shared?pool?simulator?????????????27??????77,205??????77,178
STAT...physical?read?total?byt??????81,920???????????0?????-81,920
STAT...cell?physical?IO?interc??????81,920???????????0?????-81,920
STAT...physical?read?bytes??????????81,920???????????0?????-81,920
LATCH.row?cache?objects????????????????809?????182,273?????181,464
LATCH.shared?pool???????????????????20,393?????455,102?????434,709

Run1?latches?total?versus?runs?--?difference?and?pct
Run1????????Run2????????Diff???????Pct
86,743?????925,525?????838,782??????9.37%

PL/SQL?過程已成功完成。

結(jié)果清楚地顯示，從墻上時鐘來看，proc2(沒有使用綁定變量)插入10000行記錄的時間要比proc1(使用了綁定變量)要多出很多。實(shí)際上，proc2需要的時間是proc1的15倍多，這說明，在這種情況下，對于每個"無綁定變量"的insert，執(zhí)行語句所需時間中有14/15僅用于解析！

可以看到，如果使用了綁定變量，則只有4次解析；沒有使用綁定變量，卻有不下10000次的硬解析(每次插入都會帶來一次硬解析).