创建一张表t
drop table t;
create table t (i int);
创建两个存储过程,主要是实现的是,proc1使用绑定变量,像表中插入1000条数据。
proc2使用常量向表中插入数据。
create or replace procedure proc1 as
begin
for i in 1 .. 10000 loop
execute immediate 'insert into t values( :x )' using i;
commit;
end loop;
end;
/
create or replace procedure prod2 as
begin
for i in 1 .. 10000 loop
execute immediate 'insert into t values (' || i || ')';
commit;
end loop;
end;
/
使用 runstats_pkg 来比较两者的性能。
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed
SQL> exec proc1;
PL/SQL procedure successfully completed
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed
SQL> exec prod2;
PL/SQL procedure successfully completed
SQL> exec runstats_pkg.rs_stop(9500);
----------------------------------------------------------------------
CPU TIME
Run1 CPU_TIME : 159 cpu hsecs
Run2 CPU_TIME : 1030 cpu hsecs
run1 in 15.44% of the time
Name Run1 Run2 Diff
----------------------------------------------------------------------
LATCH...row cache objects 226 180294 180068
LATCH...kks stats 35 65319 65284
LATCH...shared pool 20277 440874 420597
LATCH...shared pool simulator 57 112473 112416
LATCH...cache buffers chains 220935 281891 60956
LATCH...redo writing 14896 27624 12728
LATCH...redo allocation 34895 47601 12706
LATCH...session allocation 10 30022 30012
LATCH...session idle bit 34 10038 10004
LATCH...messages 12337 27879 15542
LATCH...enqueues 44 20060 20016
LATCH...enqueue hash chains 40061 60077 20016
STAT...sql area evicted 4 10015 10011
STAT...session cursor cache hi -45514 10071 55585
STAT...parse count (total) 30 10073 10043
STAT...parse count (hard) 4 10010 10006
STAT...IMU Redo allocation siz 18744 30084 11340
STAT...calls to kcmgcs 41 10035 9994
STAT...redo size 4918448 5106756 188308
STAT...file io wait time 53943 0 -53943
STAT...recursive calls 30218 61199 30981
STAT...session logical reads 40326 70705 30379
STAT...session uga memory -65488 65488 130976
STAT...session pga memory 0 65536 65536
STAT...enqueue requests 20028 30035 10007
STAT...enqueue releases 20029 30035 10006
STAT...db block gets 40218 60200 19982
STAT...db block gets from cach 40218 60200 19982
STAT...db block gets from cach 63 20039 19976
STAT...consistent gets 108 10505 10397
STAT...consistent gets from ca 108 10505 10397
STAT...consistent gets from ca 82 10353 10271
----------------------------------------------------------------------
Latch
Run1 Run2 Diff Pct
471224 1448175 976951 33.00%
----------------------------------------------------------------------
PL/SQL procedure successfully completed
总结:1、根据结果显示,不适用绑定变量比使用绑定变量占用的时间更长,占用CPU多一个数量级。
根据报告显示,使用绑定变量发生4次硬解析,但是不适用绑定变量要发生10010次硬解析。
2、使用绑定变量的好处:①解决了SQL注入的问题②提高数据库性能③提高可扩展性。