PL/SQL中繫結變數使用的簡單測試

透過以下簡單的實驗可以理解繫結變數的作用，對比SQL語句在不使用和使用繫結變數情況下的解析執行情況。

建立測試表

create table scott.t1(i number);

檢視當前會話的解析次數統計

select name, value from v$mystat natural join v$statname where name like 'parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                            0
parse time elapsed                                                        0
parse count (total)                                                      23
parse count (hard)                                                        2
parse count (failures)                                                    0
parse count (describe)                                                    0

不使用繫結變數執行插入

begin
    for i in 1 .. 10 loop
        execute immediate 'insert into scott.t1 values(' || i || ')';
    end loop commit;
end;
/

檢視當前會話的解析次數統計，硬解析的增加

select name, value from v$mystat natural join v$statname where name like 'parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                            3
parse time elapsed                                                        2
parse count (total)                                                      38
parse count (hard)                                                       13
parse count (failures)                                                    0
parse count (describe)                                                    0

透過檢視v$sqlarea檢視SQL語句的解析執行統計，可以看到這些不能共享的SQL，每條語句都只執行了一次。重複的解析資源消耗，也佔用了共享記憶體空間來儲存這些不同的SQL程式碼。

col sql_text for a50
select sql_text, version_count, parse_calls, executions from v$sqlarea where sql_text like 'insert into scott.t1%';
 
SQL_TEXT                                           VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ------------- ----------- ----------
insert into scott.t1 values(8)                                 1           1          1
insert into scott.t1 values(6)                                 1           1          1
insert into scott.t1 values(2)                                 1           1          1
insert into scott.t1 values(10)                                1           1          1
insert into scott.t1 values(4)                                 1           1          1
insert into scott.t1 values(5)                                 1           1          1
insert into scott.t1 values(9)                                 1           1          1
insert into scott.t1 values(1)                                 1           1          1
insert into scott.t1 values(7)                                 1           1          1
insert into scott.t1 values(3)                                 1           1          1

重構測試表，對比第二次測試

drop table scott.t1 purge;
create table scott.t1(i number);

檢視當前會話的解析次數統計

select name, value from v$mystat natural join v$statname where name like 'parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                            8
parse time elapsed                                                       16
parse count (total)                                                     132
parse count (hard)                                                       20
parse count (failures)                                                    0
parse count (describe)                                                    0

使用繫結變數執行插入

begin
    for i in 1 .. 10 loop
        execute immediate 'insert into scott.t1 values(:v1)'
            using i;
    end loop commit;
end;
/

檢視當前會話的解析次數統計，硬解析明顯減少

select name, value from v$mystat natural join v$statname where name like 'parse%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           10
parse time elapsed                                                       17
parse count (total)                                                     138
parse count (hard)                                                       22
parse count (failures)                                                    0
parse count (describe)                                                    0

檢視SQL語句的解析執行統計，使用繫結變數的SQL解析一次，執行了10次，這就是繫結變數的優勢所在。

col sql_text for a50
select sql_text, version_count, parse_calls, executions from v$sqlarea where sql_text like 'insert into scott.t1%';
 
SQL_TEXT                                           VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ------------- ----------- ----------
insert into scott.t1 values(8)                                 1           1          1
insert into scott.t1 values(6)                                 1           1          1
insert into scott.t1 values(2)                                 1           1          1
insert into scott.t1 values(10)                                1           1          1
insert into scott.t1 values(4)                                 1           1          1
insert into scott.t1 values(5)                                 1           1          1
insert into scott.t1 values(9)                                 1           1          1
insert into scott.t1 values(1)                                 1           1          1
insert into scott.t1 values(:v1)                               1           1         10
insert into scott.t1 values(7)                                 1           1          1
insert into scott.t1 values(3)                                 1           1          1