ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用绑定变量和不用绑定变量的差别

用绑定变量和不用绑定变量的差别

原创 Linux操作系统 作者:279531451 时间:2009-09-03 16:14:10 0 删除 编辑
今天看了TOM大师写的<> 一书
测试一下用绑定变量和不用绑定变量的差别
(1) 创建视图 stat
create or replace view stats as
select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch;
/
(2) 创建临时表
-- Create table
create global temporary table RUN_STATS
(
  RUNID VARCHAR2(15),
  NAME  VARCHAR2(80),
  VALUE INTEGER
)
on commit preserve rows;
/
(3) 创建包体
create or replace package runstats_pkg
 as
 procedure rs_start;
 procedure rs_middle;
 procedure rs_stop( p_difference_threshold in number default 0 );
end;
/
(4)   创建包体
create or replace package body runstats_pkg as
  g_start number;
  g_run1  number;
  g_run2  number;
  procedure rs_start is
  begin
    delete from run_stats;
    insert into run_stats
      select 'before', stats.* from stats;
    g_start := dbms_utility.get_time;
  end;
  procedure rs_middle is
  begin
    g_run1 := (dbms_utility.get_time - g_start);
    insert into run_stats
      select 'after 1', stats.* from stats;
    g_start := dbms_utility.get_time;
  end;
  procedure rs_stop(p_difference_threshold in number default 0) is
  begin
    g_run2 := (dbms_utility.get_time - g_start);
    dbms_output.put_line('Run1 ran in ' || g_run1 || ' hsecs');
    dbms_output.put_line('Run2 ran in ' || g_run2 || ' hsecs');
    dbms_output.put_line('run 1 ran in ' ||
                         round(g_run1 / g_run2 * 100, 2) ||
                         '% of the time');
    dbms_output.put_line(chr(9));
    insert into run_stats
      select 'after 2', stats.* from stats;
    dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 10) ||
                         lpad('Run2', 10) || lpad('Diff', 10));
    for x in (select rpad(a.name, 30) ||
                     to_char(b.value - a.value, '9,999,999') ||
                     to_char(c.value - b.value, '9,999,999') ||
                     to_char(((c.value - b.value) - (b.value - a.value)),
                             '9,999,999') data
                from run_stats a, run_stats b, run_stats c
               where a.name = b.name
                 and b.name = c.name
                 and a.runid = 'before'
                 and b.runid = 'after 1'
                 and c.runid = 'after 2'
                 and (c.value - a.value) > 0
                 and abs((c.value - b.value) - (b.value - a.value)) >
                     p_difference_threshold
               order by abs((c.value - b.value) - (b.value - a.value))) loop
      dbms_output.put_line(x.data);
    end loop;
    dbms_output.put_line(chr(9));
    dbms_output.put_line('Run1 latches total versus runs -- difference and pct');
    dbms_output.put_line(lpad('Run1', 10) || lpad('Run2', 10) ||
                         lpad('Diff', 10) || lpad('Pct', 8));
    for x in (select to_char(run1, '9,999,999') ||
                     to_char(run2, '9,999,999') ||
                     to_char(diff, '9,999,999') ||
                     to_char(round(run1 / run2 * 100, 2), '999.99') || '%' data
                from (select sum(b.value - a.value) run1,
                             sum(c.value - b.value) run2,
                             sum((c.value - b.value) - (b.value - a.value)) diff
                        from run_stats a, run_stats b, run_stats c
                       where a.name = b.name
                         and b.name = c.name
                         and a.runid = 'before'
                         and b.runid = 'after 1'
                         and c.runid = 'after 2'
                         and a.name like 'LATCH%')) loop
      dbms_output.put_line(x.data);
    end loop;
  end;
end;
/
(4) drop table t;
(5) 16:04:28 test@ORCLDEV> create table t (x int);
 Table created.
(6) 创建过程proc1 (用绑定变量)
16:04:40 test@ORCLDEV> create or replace procedure proc1
16:05:01   2  as
16:05:03   3  begin
16:05:08   4    for i in 1 .. 10000
16:05:16   5    loop
16:05:20   6       execute immediate
16:05:30   7       ' insert into t values (:x) ' using i ;
16:05:50   8    end loop;
16:05:57   9  end ;
16:06:00  10  /
Procedure created.
 
(7) 创建过程proc2 (不用绑定变量)
16:07:41 test@ORCLDEV> create or replace procedure proc2
16:08:00   2  as
16:08:00   3  begin
16:08:02   4  for i 1 .. 10000
16:08:14   5  loop
16:08:15   6     execute immediate
16:08:27   7     ' insert into t values ('||i||')';
16:08:50   8  end loop;
16:08:53   9  end ;
16:08:55  10  /
Warning: Procedure created with compilation errors.
16:08:56 test@ORCLDEV> show error;
Errors for PROCEDURE PROC2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7  PLS-00103: Encountered the symbol "1" when expecting one of the
  following:
  in
  The symbol "in" was substituted for "1" to continue.
16:09:06 test@ORCLDEV> ed
Wrote file afiedt.buf
  1  create or replace procedure proc2
  2  as
  3  begin
  4  for i in  1 .. 10000
  5  loop
  6 execute immediate
  7 ' insert into t values ('||i||')';
  8  end loop;
  9* end ;
16:09:22 test@ORCLDEV> /
Procedure created.
 
结果:
16:09:23 test@ORCLDEV> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
16:12:35 test@ORCLDEV> exec proc1;
PL/SQL procedure successfully completed.
16:12:42 test@ORCLDEV> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
16:12:47 test@ORCLDEV> exec proc2;
PL/SQL procedure successfully completed.
16:12:54 test@ORCLDEV> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1218 hsecs
Run2 ran in 1295 hsecs
run 1 ran in 94.05% of the time
 
Name        Run1      Run2 Diff
STAT...redo size        2,464,516 2,460,752    -3,764
STAT...consistent gets from ca      137    10,087     9,950
STAT...consistent gets       137    10,087     9,950
STAT...calls to get snapshot s       85    10,043     9,958
LATCH.library cache       371    10,347     9,976
STAT...parse count (total)       33    10,021     9,988
STAT...session cursor cache hi       21    10,015     9,994
STAT...db block gets from cach   10,532    30,393    19,861
STAT...db block gets    10,532    30,393    19,861
STAT...session logical reads   10,669    40,480    29,811
LATCH.cache buffers chains   51,816   111,361    59,545
STAT...session pga memory max  196,608    65,536  -131,072
STAT...session uga memory max  249,588    65,464  -184,124
 
Run1 latches total versus runs -- difference and pct
Run1   Run2     Diff     Pct
56,464  125,620    69,156  44.95%
PL/SQL procedure successfully completed.
显然用绑定变量快!!
~  
 
 

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

请登录后发表评论 登录
全部评论

注册时间:2009-09-01

  • 博文量
    100
  • 访问量
    153912