oracle绑定变量与非绑定变量的性能对比

create table t ( x int );

create or replace procedure proc1

as

begin

for i in 1 .. 10000

loop

execute immediate

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

end loop;

end;

 /

create or replace procedure proc2

as

begin

for i in 1 .. 10000

loop

execute immediate

'insert into t values ( '||i||')';

end loop;

end;

/

/* 这里用到的包在上一篇文章里 */

exec runstats_pkg.rs_start;

exec proc1;

exec runstats_pkg.rs_middle;

exec proc2;

exec runstats_pkg.rs_stop(10000);

Run2 ran in 723 cpu hsecs

run 1 ran in 20.06% of the time

Name                                  Run1        Run2        Diff

STAT...enqueue requests                 32      10,062      10,030

STAT...enqueue releases                 30      10,062      10,032

STAT...parse count (hard)                4      10,053      10,049

STAT...parse count (total)              11      10,061      10,050

STAT...consistent gets from ca          33      10,344      10,311

STAT...session logical reads        10,387      20,851      10,464

STAT...consistent gets from ca          55      10,581      10,526

STAT...consistent gets                  55      10,581      10,526

LATCH.cache buffers chains          51,092      61,884      10,792

LATCH.enqueue hash chains              135      20,383      20,248

LATCH.enqueues                         122      20,379      20,257

STAT...recursive calls              10,080      41,229      31,149

LATCH.kks stats                          9      57,015      57,006

LATCH.row cache objects                180      92,241      92,061

LATCH.shared pool simulator         20,125     172,276     152,151

LATCH.shared pool                   20,241     357,950     337,709

STAT...physical read total byt           0     598,016     598,016

STAT...physical read bytes               0     598,016     598,016

Run1 latches total versus runs -- difference and pct

Run1        Run2        Diff       Pct

92,892     789,588     696,696     11.76%