runstats是由Thomas Kyte开发的脚本，该脚本能对做同一件事的两个不同方法进行比较，得出孰优孰劣的结果。

 

1.授权

 

SQL> grant select on v_$statname to livan;Grant succeeded.SQL> grant select on v_$mystat to livan;Grant succeeded.SQL> grant select on v_$timer to livan;Grant succeeded.SQL> grant select on v_$latch to livan;Grant succeeded.

 

2.创建视图

SQL> conn livan/livanConnected.SQL> create or replace view stats  2  as 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  7  from v$latch  8  union all  9  select 'STAT...Elapsed Time',hsecs from v$timer;View created.

 

3.创建统计结果临时表

SQL> create global temporary table run_stats  2  ( runid varchar2(15),  3  name varchar2(80),  4  value int)  5  on commit preserve rows;Table created.

 

4.创建runstats包

 

--runstats开始调用rs_start

--rs_middle在中间调用

--完成时调用rs_stop，并打印报告

 

--创建包头

SQL> create or replace package runstats_pkg  2  as  3      procedure rs_start;  4      procedure rs_middle;  5      procedure rs_stop(p_difference_threshold in number default 0);  6  end;  7  /Package created.

 

--创建包体

[oracle@std ~]$ vi body_runstats_pkg.sql    create or replace package  body runstats_pkgas    g_start number;    g_run1 number;    g_run2 number;procedure rs_startisbegin    delete from run_stats;    insert into run_stats    select 'before',stats.* from stats;    g_start := dbms_utility.get_cpu_time;end;procedure rs_middleisbegin    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)isbegin    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 (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,'9,999,999')||       to_char(run2,'9,999,999')||       to_char(diff,'9,999,999')||       to_char(round(run1/decode(run2,0,to_number(0),run2*100,2)),'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;"body_runstats_pkg.sql" [New] 95L, 2589C writtenSQL> @body_runstats_pkg.sql 96  /Package body created.

 

 

5.测验

 

SQL> execute runstats_pkg.rs_start;PL/SQL procedure successfully completed.SQL> insert into t1 select * from dba_objects;72898 rows created.SQL> commit;Commit complete.SQL> execute runstats_pkg.rs_middle;PL/SQL procedure successfully completed.SQL> begin  2  for x in (select * from dba_objects)  3  loop  4  insert into t2 values x;  5  end loop;  6  commit;  7  end;  8  /PL/SQL procedure successfully completed.SQL> execute runstats_pkg.rs_stop(10000000);PL/SQL procedure successfully completed.SQL> set serveroutput onSQL> execute runstats_pkg.rs_stop(10000000);Run1 ran in60cpu hsecsRun2 ran in425cpu hsecsrun 1 ran in14.12% of the timeName                                  Run1        Run2        DiffSTAT...redo size                 8,577,680  29,178,972  20,601,292STAT...redo size                 8,577,680  29,183,900  20,606,220Run1 latches total versus runs -- difference and pctRun1        Run2        Diff       Pct146,010 1,069,172   923,162%PL/SQL procedure successfully completed.

 

 

show_space过程参考博主：

http://blog.csdn.net/huang_xw/article/details/7015349