TOM大师的Runstats包是一个很好的基准测试工具包。根据自己的理解,讲讲它的使用方法。
一 授权
由于Runstats包调用了几个系统视图,需要对这些视图进行授权,这些视图包括v_$timer、v_$mystat、v_$statname和v_$latch。以用户terry为例。
grant select on sys.v_$timer to terry;
grant select on v_$mystat to terry;
grant select on sys.v_$statname to terry;
grant select on sys.v_$latch to terry;
二 安装Runstats包
1 创建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;
2 创建run_stats临时表
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int
)on commit preserve rows;
3 创建Runstats包头
create or replace package runstats
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0);
end;
4 创建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;
至此,Runstats包安装完毕。
三、使用Runstats包进行简单测试。
1 建立一个简单测试表
SQL> create table t1(x int);
表已创建。
2 编写测试代码
CREATE TABLE R1 AS SELECT * FROM SCOTT.LARGE_TABLE WHERE 1=2;
CREATE TABLE R2 AS SELECT * FROM SCOTT.LARGE_TABLE WHERE 1=2;
exec runstats_pkg.rs_start;
INSERT INTO R1 SELECT * FROM SCOTT.LARGE_TABLE;
COMMIT;
exec runstats_pkg.rs_middle;
SQL> begin
2 for i in (select * from scott.large_table) loop
3 insert into r2 values i;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed
SQL> SET SERVEROUTPUT ON
SQL> exec runstats_pkg.rs_stop(100000000);
Run1 ran in 4076 hsecs
Run2 ran in 30109 hsecs
run 1 ran in 13.54% of the time
Name Run1 Run2 Diff
STAT...redo size 49,960,116 286,817,232 236,857,116
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
313,113 6,573,705 6,260,592 4.76%
PL/SQL procedure successfully completed
SQL> exec runstats_pkg.rs_stop(10000000);
Run1 ran in 4076 hsecs
Run2 ran in 30910 hsecs
run 1 ran in 13.19% of the time
Name Run1 Run2 Diff
STAT...undo change vector size 2,762,836 68,013,056 65,250,220
STAT...undo change vector size 2,762,836 68,015,612 65,252,776
STAT...redo size 49,960,116 286,817,232 236,857,116
STAT...redo size 49,960,116 286,820,756 236,860,640
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
626,226 13,149,218 12,522,992 4.76%
PL/SQL procedure successfully completed