runstats是tom写的一款很好的基准测试小工具。其安装及测试示例如下。

以下测试工具为SQL TOOLS。

1./*以sys登陆，给u1cp授权四个视图的权限*/
grant select on v_$latch to u1cp;
grant select on v_$mystat to u1cp;
grant select on V_$timer to u1cp;
grant select on v_$statname to u1cp;

2./*在u1cp下建立sys视图的同义词*/
CREATE SYNONYM v_$latch FOR sys.v_$latch;
CREATE SYNONYM v_$mystat FOR sys.v_$mystat;
CREATE SYNONYM V_$timer FOR sys.V_$timer;
CREATE SYNONYM v_$statname FOR sys.v_$statname;


3./*以u1cp登陆创建自己的统计视图。v$为v_$的同义词，v_$才是实际的底层视图，之前授权的就是v_$*/
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;


4./*创建run_stats临时表*/
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int
)on commit preserve rows;


5./*创建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/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; 



--=================================测试==================================

create table t1 (id number);
create table t2 (id number);

exec runstats_pkg.rs_start;
Begin
for i in 1..10000 Loop
insert into t1 values(i);
end loop;
end; 


exec runstats_pkg.rs_middle;
Begin
for i in 1..10000 Loop
Execute Immediate 'insert into t2 values('||i||')';
end loop;
end;--SQL拼接
exec runstats_pkg.rs_stop;


output:

18  PL/SQL block, executed in 0.172 sec.                              
    Run1 ran in 2 cpu hsecs