runstats工具的配置及使用

runstats是tom写的一个用来比较同一事件的不同方法的工具。

今天测试一个语句用group by和不用效率有何不同时，头儿建议用runstats来看。

第一次用，很好很强大。

1、以sys用户登录，执行下列分配权限语句。要执行runstats的用户叫zhaoss

grant select on sys.v_$timer to zhaoss;

grant select on v_$mystat to zhaoss;

grant select on sys.v_$statname to zhaoss;

grant select on sys.v_$latch to zhaoss;

2、以zhaoss用户登录，执行下列语句创建runstats包：

a>创建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;

b>创建run_stats全局临时表：

create   global   temporary   table   run_stats

( runid   varchar2 (   15 ),

    name   varchar2 ( 80   ),

    value   int

)  

on   commit   preserve   rows ;

c>创建runstats包定义：

rs_start：执行第一种方法前使用，用来记录执行前状态；

rs_middle：执行完第一种方法后，执行第二种方法前执行，用来记录执行完第一种方法后状态；

rs_stop：执行完第二种方法后使用，用来生成最终结果。

create   or   replace   package   runstats   as

    procedure   rs_start;

    procedure   rs_middle;

    procedure   rs_stop(p_difference_threshold   in   number   default   0 );

end ;

d>创建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 ;

使用说明：

可以使用

set serveroutput on

或者 

spool 1.txt   

set serveroutput on

spool off

来显示或在文件保存输出结果。

SQL> exec runstats.rs_start;

SQL> 第一个执行语句;

SQL> exec runstats.rs_middle;

SQL> 第二个执行语句;

SQL> exec runstats.rs_stop;

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

转载于:http://blog.itpub.net/24496749/viewspace-730071/