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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2138279/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2138279/