[導讀]oracle將已解析、已編譯的SQL連同其他內容存儲在共享池(shared pool)中，這是系統(tǒng)全局區(qū)(System Global Area,SGA)中一個非常重要的共享內存結構.綁定變量(bind
oracle將已解析、已編譯的SQL連同其他內容存儲在共享池(shared pool)中，這是系統(tǒng)全局區(qū)(System Global Area,SGA)中一個非常重要的共享內存結構.
綁定變量(bind variable)是查詢中的一個占位符。
例如，要獲取員工編號7369的相應記錄，可以使用：
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設置為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ù)庫看來以前從未見過，必須對查詢進行解析、限定(命名解析)、安全性檢查、優(yōu)化等。你執(zhí)行的每條不同的語句都要在執(zhí)行時進行編譯。
第二個查詢使用了一個綁定變量: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)建。
第二個過程則分別要為插入的每一行構造一條獨特的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以上的比較結果 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?過程已成功完成。
結果清楚地顯示，從墻上時鐘來看，proc2(沒有使用綁定變量)插入10000行記錄的時間要比proc1(使用了綁定變量)要多出很多。實際上，proc2需要的時間是proc1的15倍多，這說明，在這種情況下，對于每個"無綁定變量"的insert，執(zhí)行語句所需時間中有14/15僅用于解析！
可以看到，如果使用了綁定變量，則只有4次解析；沒有使用綁定變量，卻有不下10000次的硬解析(每次插入都會帶來一次硬解析).
下載文檔
