ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 绑定变量对性能的影响

绑定变量对性能的影响

原创 Linux操作系统 作者:279531451 时间:2009-11-06 22:21:59 0 删除 编辑

declare
l_number number;
begin
for i in 1 .. 10000
loop
l_number:=dbms_random.random;
execute immediate
'insert into t values('||l_number||','||l_number||','||l_number||','||l_number||')';
end loop;
commit;
end;
 /

select a.event,
    (a.total_waits-nvl(b.total_waits,0)) total_waits,
    (a.time_waited-nvl(b.time_waited,0)) time_waited
    from (select *
      from v$session_event
      where sid=(select sid from v$mystat where rownum=1)) a,
     sess_event b
    where a.event=b.event(+)
   and (a.total_waits-nvl(b.total_waits,0))>0
 /
SQL> define _editor=vi;
SQL> ed
已写入 file afiedt.buf

  1  create global temporary table sess_event
  2  on commit preserve rows
  3  as
  4* select * from v$session_event where 1=0
SQL> /

表已创建。
创建一个表
SQL> ed
已写入 file afiedt.buf

  1  create table t
  2  (c1 int ,c2 int ,c3 int ,c4 int )
  3* storage(freelists 10)
SQL> /

表已创建。
SQL> insert into sess_event
  2  select * from v$session_event
  3  where sid = (select sid from v$mystat where rownum=1);

已创建7行。
SQL> declare
  2  l_number number;
  3  begin
  4    for i in 1 .. 10000
  5    loop
  6      l_number:=dbms_random.random;
  7      execute immediate
  8      'insert into t values('||l_number||','||l_number||','||l_number||','||l_number||')';
  9    end loop;
 10    commit;
 11  end;
 12  /

PL/SQL 过程已成功完成。 
SQL> ed
已写入 file afiedt.buf

  1  select a.event,
  2  (a.total_waits-nvl(b.total_waits,0)) total_waits,
  3  (a.time_waited-nvl(b.time_waited,0)) time_waited
  4  from (select *
  5    from v$session_event
  6    where sid=(select sid from v$mystat where rownum=1)) a,
  7   sess_event b
  8  where a.event=b.event(+)
  9* and (a.total_waits-nvl(b.total_waits,0))>0
SQL> /
SQL> col event format a50
SQL> /

EVENT         TOTAL_WAITS TIME_WAITED
-------------------------------------------------- ----------- -----------
log file sync           1   0
db file sequential read        154  37
SQL*Net message to client         7   0
SQL*Net message from client         6      33394
SQL*Net break/reset to client         4   0
events in waitclass Other         6   0

已选择6行。
看到如果一个用户执行,没有出现空闲锁存器的等待事件
如果两个用户同时执行上述dml语句. 结果如下:
SQL> /

EVENT         TOTAL_WAITS TIME_WAITED
-------------------------------------------------- ----------- -----------
log file sync           2   2
db file sequential read        292  55
SQL*Net message to client        24   0
SQL*Net message from client        23      49075
SQL*Net break/reset to client         7   2
latch: shared pool          4  41
events in waitclass Other        29  23

已选择7行。
两个用户同时执行出现了latch: shared pool 的事件等待
三个用户:
EVENT         TOTAL_WAITS TIME_WAITED
-------------------------------------------------- ----------- -----------
log file sync           3   2
db file sequential read        428  62
SQL*Net message to client        27   0
SQL*Net message from client        26      84191
SQL*Net break/reset to client         7   2
library cache: mutex X          2  40
latch: shared pool         11        142
events in waitclass Other        32  33
下面我使用绑定变量:三个用户同时执行

使用绑定变量:
declare
l_number number;
begin
for i in 1 .. 10000
loop
l_number:=dbms_random.random;
execute immediate 'insert into t values(:x1,:x2,:x3,:x4)' using l_number,l_number,l_number,l_number;
end loop;
commit;
end;
/
EVENT         TOTAL_WAITS TIME_WAITED
-------------------------------------------------- ----------- -----------
log file sync           1   4
db file sequential read        137   8
SQL*Net message to client         3   0
SQL*Net message from client         2       2747
events in waitclass Other         1   0
没有空闲锁存器带等事件
使用绑定变量可以提高很大的性能
总结:
(1) 使用绑定变量对性能改善非常关键
(2) 避免查询的软分析同样很重要

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

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

注册时间:2009-09-01

  • 博文量
    100
  • 访问量
    154558