Tom大师的调优脚本－－ Runstats 的使用方法

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

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

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

二 安装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 编写测试代码

CREATE TABLE R1 AS SELECT * FROM SCOTT.LARGE_TABLE WHERE 1=2;
CREATE TABLE R2 AS SELECT * FROM SCOTT.LARGE_TABLE WHERE 1=2;

exec runstats_pkg.rs_start;

INSERT INTO R1 SELECT * FROM SCOTT.LARGE_TABLE;

COMMIT;

exec runstats_pkg.rs_middle;

SQL> begin
2     for i in (select * from scott.large_table) loop
3       insert into r2 values i;
4     end loop;
5     commit;
6   end;
7 /

PL/SQL procedure successfully completed

SQL> SET SERVEROUTPUT ON
SQL> exec runstats_pkg.rs_stop(100000000);

Run1 ran in 4076 hsecs
Run2 ran in 30109 hsecs
run 1 ran in 13.54% of the time

Name                                  Run1        Run2        Diff
STAT...redo size                49,960,116 286,817,232 236,857,116

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     313,113   6,573,705   6,260,592      4.76%

PL/SQL procedure successfully completed

SQL> exec runstats_pkg.rs_stop(10000000);

Run1 ran in 4076 hsecs
Run2 ran in 30910 hsecs
run 1 ran in 13.19% of the time

Name                                  Run1        Run2        Diff
STAT...undo change vector size   2,762,836 68,013,056 65,250,220
STAT...undo change vector size   2,762,836 68,015,612 65,252,776
STAT...redo size                49,960,116 286,817,232 236,857,116
STAT...redo size                49,960,116 286,820,756 236,860,640

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     626,226 13,149,218 12,522,992      4.76%

PL/SQL procedure successfully completed