繫結變數的測試

前言：        Oracle在執行SQL語句時，普遍存在以下幾個步驟：當SQL語句首次執行，Oracle將確認該句語句的語法是否正確（語法解析Syntax parse）並進一步確認語句相關表和列的存在性等因素(語義解析semantic parse)以及優化器決定執行計劃等步驟。整個過程稱之為硬解析，硬解析消耗大量的CPU時間和系統資源。硬解析過多會有效降低系統效能。        若之前已進行過硬解析，且解析後的分析樹和執行計劃仍存在於共享池中，則同樣的SQL僅需要軟解析。軟解析將輸入的SQL語句轉換為雜湊程式碼，同共享池內雜湊連結串列上的已有記錄進行對比，找出對應的遊標資訊，使用已有的執行計劃執行。繫結變數,將實際的變數值代入SQL語句中。        執行SQL語句，查詢語句將返回結果集。       不使用繫結變數的SQL語句，Oracle無法將它們視為相同的，如以下兩句語句：       select * from emp where empno=1234       select * from emp where empno=5678       因為自由變數的不同，Oracle認為以上是2句不同的語句，則當第一條被硬解析後，第二條SQL執行時仍無法避免硬解析。實際在以上不使用繫結變 量的情況中，只要自由變數有所改變則需要一次硬解析。這是強烈建議使用繫結變數的主要原因，使用繫結變數的語句變數的實際值僅在SQL執行的最後階段被代 入。如以下語句：       select * from emp where empno=:x      該語句使用繫結值:x替代自由變數，在應用中語句可能以預編譯或普通編譯的方式存在，僅在執行階段代入變數值，多次執行僅需要一次硬解析，較不使用繫結變數情況效能大大提升。 

 

測試步驟：

     測試方法：分別使用繫結變數和不使用繫結變數分別向表中插入10000條資料，測量所使用的時間：

     測試前請先執行以下指令碼：

     步驟1：set echo on;(把執行結果顯示出來) 

     步驟2：alter system flush shared_pool;這條語句是清空共項池,每次都必須使用,確保共享池是空的,以提高執行效率; 

     步驟3：set timing on(開啟記時器.)

 

一、繫結變數的測試

1、建立表A1,指令碼：create table A1(x int); 

2、帶引數的儲存過程

create or replace procedure pro1

as

begin

for i in 1 .. 100000

loop

execute immediate

'insert into A1 values(:x)' using i;

end loop;

end;

/ 

執行結果：

SYS@orcl> exec pro2;

PL/SQL procedure successfully completed.

Elapsed: 00:00:57.42

 

二、不帶繫結變數的測試

create table A2(x int); 

create or replace procedure pro2

as

begin 

for i in 1 .. 100000

loopexecute immediate'insert into A2 values('||i||')';end loop;end;執行看看結果：

SYS@orcl>exec pro1

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.60

總結：使用繫結變數的速度和不使用繫結變數相差的數量級不是同一個等級的；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12679300/viewspace-1138107/，如需轉載，請註明出處，否則將追究法律責任。