性能比较工具runStats_pkg

runStats_pkg是Tom大师写的一个脚本,
用于比较两个语句的时间,统计数据和栓锁使用情况。
脚本下载链接:
https://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

下面以edmond帐号为例,使用runStats比较绑定变量的差异

首先,给edmond帐号系统表的授权。
conn / as sysdba
create user edmond identified by edmond;
grant connect,resource to edmond;
grant create view to edmond;
grant select on sys.v_$statname to edmond;
grant select on sys.v_$mystat to edmond;
grant select on sys.v_$latch to edmond;
grant select on sys.v_$timer to edmond;

然后,执行runStats脚本
  1. create global temporary table run_stats
  2. ( runid varchar2(15),
  3.   name varchar2(80),
  4.   value int )
  5. on commit preserve rows;

  6. create or replace view stats
  7. as select 'STAT...' || a.name name, b.value
  8.       from sys.v_$statname a, sys.v_$mystat b
  9.      where a.statistic# = b.statistic#
  10.     union all
  11.     select 'LATCH.' || name, gets
  12.       from sys.v_$latch
  13.     union all
  14.     select 'STAT...Elapsed Time', hsecs from sys.v_$timer;

  15. create or replace package runstats_pkg
  16. as
  17.     procedure rs_start;
  18.     procedure rs_middle;
  19.     procedure rs_stop( p_difference_threshold in number default 0 );
  20. end;
  21. /
  22.     
  23. create or replace package body runstats_pkg
  24. as

  25. g_start number;
  26. g_run1 number;
  27. g_run2 number;

  28. procedure rs_start
  29. is
  30. begin
  31.     delete from run_stats;

  32.     insert into run_stats
  33.     select 'before', stats.* from stats;
  34.         
  35.     g_start := dbms_utility.get_cpu_time;
  36. end;

  37. procedure rs_middle
  38. is
  39. begin
  40.     g_run1 := (dbms_utility.get_cpu_time-g_start);
  41.  
  42.     insert into run_stats
  43.     select 'after 1', stats.* from stats;
  44.     g_start := dbms_utility.get_cpu_time;

  45. end;

  46. procedure rs_stop(p_difference_threshold in number default 0)
  47. is
  48. begin
  49.     g_run2 := (dbms_utility.get_cpu_time-g_start);

  50.     dbms_output.put_line
  51.     ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
  52.     dbms_output.put_line
  53.     ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
  54.         if ( g_run2 <> 0 )
  55.         then
  56.     dbms_output.put_line
  57.     ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
  58.       '% of the time' );
  59.         end if;
  60.     dbms_output.put_line( chr(9) );

  61.     insert into run_stats
  62.     select 'after 2', stats.* from stats;

  63.     dbms_output.put_line
  64.     ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
  65.       lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

  66.     for x in
  67.     ( select rpad( a.name, 30 ) ||
  68.              to_char( b.value-a.value, '999,999,999' ) ||
  69.              to_char( c.value-b.value, '999,999,999' ) ||
  70.              to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
  71.         from run_stats a, run_stats b, run_stats c
  72.        where a.name = b.name
  73.          and b.name = c.name
  74.          and a.runid = 'before'
  75.          and b.runid = 'after 1'
  76.          and c.runid = 'after 2'
  77.          -- and (c.value-a.value) > 0
  78.          and abs( (c.value-b.value) - (b.value-a.value) )
  79.                > p_difference_threshold
  80.        order by abs( (c.value-b.value)-(b.value-a.value))
  81.     ) loop
  82.         dbms_output.put_line( x.data );
  83.     end loop;

  84.     dbms_output.put_line( chr(9) );
  85.     dbms_output.put_line
  86.     ( 'Run1 latches total versus runs -- difference and pct' );
  87.     dbms_output.put_line
  88.     ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
  89.       lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

  90.     for x in
  91.     ( select to_char( run1, '999,999,999' ) ||
  92.              to_char( run2, '999,999,999' ) ||
  93.              to_char( diff, '999,999,999' ) ||
  94.              to_char( round( run1/decode( run2, 0, to_number(null), run2) *100,2 ), '99,999.99' ) || '%' data
  95.         from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
  96.                       sum( (c.value-b.value)-(b.value-a.value)) diff
  97.                  from run_stats a, run_stats b, run_stats c
  98.                 where a.name = b.name
  99.                   and b.name = c.name
  100.                   and a.runid = 'before'
  101.                   and b.runid = 'after 1'
  102.                   and c.runid = 'after 2'
  103.                   and a.name like 'LATCH%'
  104.                 )
  105.     ) loop
  106.         dbms_output.put_line( x.data );
  107.     end loop;
  108. end;

  109. end;
  110. /
接下来创建测试表和过程
create table t(x int);
insert into t select rownum from dual connect by level<=10000;

没有绑定变量的测试过程
create or replace procedure p1
as
    l_cnt number;
begin
    for i in 1 .. 10000
    loop
        execute immediate 'select count(*) from t where x=' || i into l_cnt;
    end loop;
end;
/

绑定变量的测试过程
create or replace procedure p2
as
    l_cnt number;
begin
    for i in 1 .. 10000
    loop
        select count(*) into l_cnt from t where x=i;
    end loop;
end;
/

开始测试:
set serveroutput on;
alter system flush shared_pool;

exec runStats_pkg.rs_start;
exec p1;
exec runStats_pkg.rs_middle;
exec p2;
exec runStats_pkg.rs_stop(1000);

测试结果
可以看到第一个过程解析了近1w次,而第二个过程仅仅解析了7次
非绑定变量的方式导致了大量的栓锁使用.

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

转载于:http://blog.itpub.net/29254281/viewspace-1273911/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值