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

首先初始化数据

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  &nbs