TOM的Runstats包的使用方法

 TOM的Runstats包是一个很好的基准测试工具包。根据自己的理解，讲讲它的使用方法。

一 授权
由于Runstats包调用了几个系统视图，需要对这些视图进行授权，这些视图包括v_$timer、v_$mystat、v_$statname和v_$ latch。以用户aaa为例。

    grant select on sys.v_$timer to aaa;
    grant select on v_$mystat to aaa;
    grant select on sys.v_$statname to aaa;
    grant select on sys.v_$ latch to aaa;

二 安装Runstats包
1 创建stats视图
    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 创建run_stats临时表
    create global temporary table run_stats
    ( runid   varchar2(15),
      name    varchar2(80),
      value   int
    ) on commit preserve rows;

3 创建Runstats包头
    create or replace package runstats
    as
        procedure rs_start;
        procedure rs_middle;
        procedure rs_stop( p_difference_threshold in number default 0);
    end;

4 创建Runstats包体
create or replace package body runstats
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);
    --add a line here to avoid ora-20000
    dbms_output.enable(480000);
    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', 12 ) ||
      lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

    for x in
    ( select rpad( a.name, 30 ) ||
             to_char( b.value-a.value, '999,999,999' ) ||
             to_char( c.value-b.value, '999,999,999' ) ||
             to_char( ( (c.value-b.value)-(b.value-a.value)), '999,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', 12 ) || lpad( 'Run2', 12 ) ||
      lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

    for x in
    ( select to_char( run1, '999,999,999' ) ||
             to_char( run2, '999,999,999' ) ||
             to_char( diff, '999,999,999' ) ||
             to_char( round( run1/run2*100,2 ), '99,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;

至此，Runstats包安装完毕。

三 使用Runstats包进行简单测试。
1 建立一个简单测试表
    SQL> create table t1(x int);
    表已创建。

2 编写测试代码
    begin
       runstats.rs_start;
       -- 测试1
       for i in 1..10000 loop
           execute immediate
               'insert into t1 values(' || to_char(i) || ')';
       end loop;
       runstats.rs_middle;

       -- 测试2
       for i in 1..10000 loop
           insert into t1 values(i);
       end loop;
       runstats.rs_stop(500);
     end;

3 测试结果
Run1 ran in 432 hsecs
Run2 ran in 265 hsecs
run 1 ran in 163.02% of the time

Name                                  Run1        Run2        Diff
LATCH.session allocation             1,162           7      -1,155
LATCH.simulator hash latch               3       1,426       1,423
STAT..consistent gets               10,249         297      -9,952
STAT..session logical reads         20,897      10,943      -9,954
STAT..parse count (total)           10,027          37      -9,990
STAT..opened cursors cumulativ      10,024          23     -10,001
STAT..parse count (hard)            10,012           7     -10,005
LATCH.cache buffers chains          73,423      53,576     -19,847
LATCH.row cache enqueue latch       40,224         160     -40,064
LATCH.row cache objects             40,247         179     -40,068
LATCH.library cache pin alloca      62,562         231     -62,331
LATCH.child cursor hash table       71,303          95     -71,208
LATCH.library cache pin             92,772      20,412     -72,360
LATCH.shared pool                  218,138      11,350    -206,788
LATCH.library cache                242,042      20,756    -221,286

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
853,149     119,446    -733,703    714.25%

从上面的数据可以看出，不使用绑定变量，硬解析了10,012次，而使用绑定变量，只硬解析了7次。并且在系统栓锁的使用上，相隔了7倍。