绑定变量和非绑定变量的资源消耗对比

首先初始化数据

Create table tsts as select level as col1 ,rdbms_random.string(‘p’,10) as col2 from dual connect by level <= 10000 ;

create or replace procedure p1 as

rec_tsts%rowtype ;

begin

for i in1 .. 10000 loop

execute immediate 'select * from tsts where col1='||i into rec_ ;

end loop;

end ;

create or replace procedure p2 as

rec_tsts%rowtype ;

begin

for i in1 .. 10000 loop

execute immediate 'select * from tsts where col1=:1 ' into rec_ using i;

end loop;

end ;

开始比较（使用tom的runstat脚本）

exec runstats_pkg.rs_start ;

exec p1 ;

exec runstats_pkg.rs_middle ;

exec p2 ;

exec runstats_pkg.rs_stop(1000) ;

_dexter@FAKE> exec runstats_pkg.rs_start ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec p1 ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec runstats_pkg.rs_middle ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec p2 ;

PL/SQL procedure successfully completed.

_dexter@FAKE> exec runstats_pkg.rs_stop(1000);

Run1 ran in 1163 cpu hsecs

Run2 ran in 64 cpu hsecs

run 1 ran in 1817.19% of the time

Name Run1 Run2 Diff

STAT...recursive cpu usage 1,119 59 -1,060

STAT...DB time 1,150 75 -1,075

STAT...CPU used when call star 1,173 72 -1,101

STAT...CPU used by this sessio 1,172 64 -1,108

STAT...buffer is not pinned co 31,411 30,087 -1,324

STAT...consistent gets 41,986 40,457 -1,529

STAT...consistent gets from ca 41,986 40,457 -1,529

STAT...session logical reads 42,047 40,494 -1,553

STAT...sorts (rows) 1,676 3 -1,673

LATCH.cache buffers chains 66,737 61,883 -4,854

STAT...sql area evicted 9,519 4 -9,515

STAT...session cursor cache hi 360 10,018 9,658

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

STAT...enqueue releases 10,053 4 -10,049

STAT...enqueue requests 10,053 4 -10,049

STAT...parse count (total) 10,057 4 -10,053

LATCH.enqueue hash chains 20,379 110 -20,269

STAT...recursive calls 41,827 10,157 -31,670

LATCH.shared pool simulator 38,862 40 -38,822

LATCH.shared pool 455,526 10,683 -444,843

LATCH.row cache objects 513,487 441 -513,046

STAT...logical read bytes from 344,449,024331,726,848 -12,722,176

Run1 latches total versus runs -- difference andpct

Run1 Run2 Diff Pct

1,097,601 74,729 -1,022,872 1,468.78%

PL/SQL procedure successfully completed.

分析

可以看到资源使用相差是非常巨大的。我们示例中的sql语句是典型的应用在oltp系统中的，可以看到使用绑定变量消耗更少的资源。我们观察相差比较大的事件。执行可以完全肯定，绑定变量优于非绑定变量。主要由于下面几个相差较大的事件影响。

可以看到，因为硬解析，oracle数据库优化器需要重新对语句进行优化操作，需要获取更多的latch并且执行更多的操作，而且对于oltp系统中的语句，优化期间所做的操作甚至比查询数据需要更多的时间，所以在OLTP系统中，更多的使用绑定变量，是非常有必要的。

附录：tom runstat 脚本的使用