runstats —— 基准性能测试工具包！

runstats只测量三件重要的东西：
挂钟或占用的时间
系统统计数据
栓锁（此信息是该报告的关键）
方法中出现的栓锁越少，情况越好。本人可以选择一种方法，它的执行时间更长但只使用10%的栓锁。我知道使用少量栓锁的方法将比使用较多栓锁的方法更好。
runstats最好在单用户数据库中使用。

安装runstats：

SQL> conn /as sysdba
已连接。
SQL> create user test identified by test;

用户已创建。

SQL> grant dba to test;

授权成功。

SQL> grant select on sys.v_$timer to test;

授权成功。

SQL> grant select on v_$mystat to test;

授权成功。

SQL> grant select on sys.v_$statname to test;

授权成功。

SQL> grant select on sys.v_$latch to test;

授权成功。

SQL> conn test/test
已连接。
SQL> create global temporary table run_stats
  2  ( runid varchar2(15),
  3  name varchar2(80),
  4  value int
  5  )on commit preserve rows;

表已创建。

SQL> create or replace view stats as
  2  select 'STAT..' || a.name name, b.value
  3    from v$statname a, v$mystat b
  4   where a.statistic# = b.statistic#
  5  union all
  6  select 'LATCH.' || name, gets from v$latch
  7  union all
  8  select 'STAT...Elapsed Time', hsecs from v$timer;

视图已创建。

下面是runstats脚本：

create or replace package runstats as
  procedure rs_start;
  procedure rs_middle;
  procedure rs_stop(p_difference_threshold in number default 0);
end;
/
--p_difference_threshold参数的含义是：前后两个相同的项的统计结果相差的数目的绝对值大于等于该参数设定的值，该统计项才会被显示。默认值是0表示所有的结果都显示。

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包安装完毕。下面开始做测试：

SQL> create table x(id number);

表已创建。

SQL> set serverout on
SQL> exec runstats.rs_start;

PL/SQL 过程已成功完成。

SQL> begin
  2  for i in 1..1000 Loop
  3  insert into x values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> exec runstats.rs_middle;

PL/SQL 过程已成功完成。

SQL> begin
  2  for i in 1..1000 Loop
  3  execute immediate 'insert into x values('||i||')';
  4  end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> exec runstats.rs_stop(1000);
Run1 ran in 4357 hsecs
Run2 ran in 11323 hsecs
run 1 ran in 38.48% of the time

Name                                  Run1        Run2        Diff
STAT..session cursor cache hit       1,010           4      -1,006
STAT..undo change vector size       72,632      71,204      -1,428
LATCH.SQL memory manager worka       1,008       2,482       1,474
STAT..db block gets from cache       1,140       3,062       1,922
STAT..db block gets                  1,140       3,062       1,922
STAT..db block gets from cache          22       2,000       1,978
LATCH.kks stats                         18       2,019       2,001
LATCH.shared pool simulator             44       2,564       2,520
STAT..recursive calls                1,240       4,079       2,839
STAT..session logical reads          1,232       4,093       2,861
LATCH.enqueue hash chains              799       3,890       3,091
LATCH.enqueues                         762       3,866       3,104
STAT..redo size                    255,104     249,264      -5,840
LATCH.cache buffers chains           5,787      11,627       5,840
STAT...Elapsed Time                  4,359      11,325       6,966
STAT..physical read total byte       8,192           0      -8,192
STAT..physical read bytes            8,192           0      -8,192
STAT..cell physical IO interco       8,192           0      -8,192
LATCH.row cache objects                773      18,724      17,951
LATCH.shared pool                    1,677      41,385      39,708
STAT..session uga memory max       123,452      73,084     -50,368
STAT..session uga memory                 0     196,536     196,536
STAT..session pga memory                 0     262,144     262,144

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
14,109      93,135      79,026     15.15%

PL/SQL 过程已成功完成。