oracle將已解析、已編譯的SQL連同其他內(nèi)容存儲(chǔ)在共享池(shared pool)中，這是系統(tǒng)全局區(qū)(System Global Area,SGA)中一個(gè)非常重要的共享內(nèi)存結(jié)構(gòu).
綁定變量(bind variable)是查詢中的一個(gè)占位符。
例如，要獲取員工編號(hào)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?過(guò)程已成功完成。 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，然后不再查詢這個(gè)員工了。之后，你可能會(huì)查詢員工666，然后是員工888，如此等等。如果在查詢中使用直接量(常量),那么每個(gè)查詢都將是一個(gè)全新的查詢，在數(shù)據(jù)庫(kù)看來(lái)以前從未見(jiàn)過(guò)，必須對(duì)查詢進(jìn)行解析、限定(命名解析)、安全性檢查、優(yōu)化等。你執(zhí)行的每條不同的語(yǔ)句都要在執(zhí)行時(shí)進(jìn)行編譯。
第二個(gè)查詢使用了一個(gè)綁定變量:empno，變量值在查詢執(zhí)行時(shí)提供。這個(gè)查詢只編譯一次，隨后會(huì)把查詢計(jì)劃存儲(chǔ)在一個(gè)共享池(庫(kù)緩存)中，以便以后獲取和重用這個(gè)查詢計(jì)劃。
--------------------------------------------------------------------------------------------------------------------------------
第一個(gè)過(guò)程使用了帶綁定變量的sql語(yǔ)句，如下：
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??/ 過(guò)程已創(chuàng)建。
第二個(gè)過(guò)程則分別要為插入的每一行構(gòu)造一條獨(dú)特的sql語(yǔ)句，如下：
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??/
過(guò)程已創(chuàng)建。比較兩者性能區(qū)別：
scott@ORCL>exec?runstats_pkg.rs_start PL/SQL?過(guò)程已成功完成。 scott@ORCL>exec?proc1 PL/SQL?過(guò)程已成功完成。 scott@ORCL>exec?runstats_pkg.rs_middle PL/SQL?過(guò)程已成功完成。 scott@ORCL>exec?proc2 PL/SQL?過(guò)程已成功完成。 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?過(guò)程已成功完成。
結(jié)果清楚地顯示，從墻上時(shí)鐘來(lái)看，proc2(沒(méi)有使用綁定變量)插入10000行記錄的時(shí)間要比proc1(使用了綁定變量)要多出很多。實(shí)際上，proc2需要的時(shí)間是proc1的15倍多，這說(shuō)明，在這種情況下，對(duì)于每個(gè)"無(wú)綁定變量"的insert，執(zhí)行語(yǔ)句所需時(shí)間中有14/15僅用于解析！
可以看到，如果使用了綁定變量，則只有4次解析；沒(méi)有使用綁定變量，卻有不下10000次的硬解析(每次插入都會(huì)帶來(lái)一次硬解析).
下載文檔
