1. 创建临时表
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
union all
select ‘STAT...Elapsed Time‘, hsecs from v$timer;

需要授权相应的视图权限

grant select on v_$statname to &&username ;
grant select on v_$mystat to &&username ;
grant select on v_$latch  to &&username ;
grant select on v_$timer to &&username ;


2. 创建临时表

create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;

3. 创建相应的runstat包
create or replace package runstats_pkg as
  procedure rs_start;
  procedure rs_middle;
  procedure rs_stop(p_difference_threshold in number default 0);
end;
/




create or replace package body runstats_pkg 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_cpu_time;
  end;


  procedure rs_middle is
  begin
    g_run1 := (dbms_utility.get_cpu_time - g_start);
 
    insert into run_stats
      select ‘after 1‘, stats.* from stats;
    g_start := dbms_utility.get_cpu_time;
 
  end;


  procedure rs_stop(p_difference_threshold in number default 0) is
  begin
    g_run2 := (dbms_utility.get_cpu_time - g_start);
 
    dbms_output.put_line(‘Run1 ran in ‘ || g_run1 || ‘ cpu hsecs‘);
    dbms_output.put_line(‘Run2 ran in ‘ || g_run2 || ‘ cpu hsecs‘);
    if (g_run2 <> 0) then
      dbms_output.put_line(‘run 1 ran in ‘ ||
                           round(g_run1 / g_run2 * 100, 2) ||
                           ‘% of the time‘);
    end if;
    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 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 /
                                   decode(run2, 0, to_number(0), 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;
/


4. 用法

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


示例：

                                                                                dexter@REPO>exec runstats_pkg.rs_start;                                                                                                                               

PL/SQL 过程已成功完成。

dexter@REPO>delete from t1 ;

已删除 1 行。

dexter@REPO>exec runstats_pkg.rs_middle;

PL/SQL 过程已成功完成。

dexter@REPO>insert into t1 select level from dual connect by level <= 10000 ;

已创建 10000 行。

dexter@REPO>exec runstats_pkg.rs_stop(100);
Run1 ran in 1 cpu hsecs
Run2 ran in 4 cpu hsecs
run 1 ran in 25% of the time

Name                                  Run1        Run2        Diff
LATCH.session idle bit                 331         207        -124
STAT...redo entries                     15         167         152
LATCH.SQL memory manager worka       1,012         807        -205
STAT...db block changes                 77         300         223
LATCH.enqueue hash chains            1,035         794        -241
LATCH.cache buffers chains           2,182       2,425         243
STAT...db block gets                    60         304         244
STAT...db block gets from cach          60         304         244
STAT...session logical reads            89         374         285
LATCH.shared pool                    1,016         515        -501
STAT...Elapsed Time                  4,370       3,681        -689
LATCH.row cache objects              2,063       1,082        -981
STAT...undo change vector size       3,908      29,856      25,948
STAT...physical read bytes           8,192      40,960      32,768
STAT...physical read total byt       8,192      40,960      32,768
STAT...cell physical IO interc       8,192      40,960      32,768
STAT...session uga memory max      123,512      65,488     -58,024
STAT...session uga memory           65,488           0     -65,488
STAT...file io wait time             8,767     113,342     104,575
STAT...redo size                     5,220     167,168     161,948
STAT...logical read bytes from     729,088   3,063,808   2,334,720

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
11,252       8,792      -2,460    127.98%

PL/SQL 过程已成功完成。