runstats工具是《 oracle database 9i/10g/11g编程艺术 深入数据库体系结构》作者写的一个统计性能工具，能对做同一件事的两个方法进行比较，得到孰优孰劣的结果。
(看到runstats想到了db2 里有runstats命令收集统计信息)
runststs工具主要测量三个要素
- 墙上时钟（wall clock) 或耗用时间（elapsed time)
- 系统统计结果，会并排地显示每个方法做某件事（如执行一个解析调用）的次数，并展示出二者之差
- 闩定（latch）这个是报告的关键输出
要使用该工具，需要能访问V$视图，并创建一个表来存储统计结果，还需要创建runstats包，下面是在scott用户下创建该工具，以下试验在ORACLE 11.2.0.1.0上进行
使用SYS用户登录，执行以下语句
--默认scott无创建视图权限,创建视图时会报ORA-01031: insufficient privileges
grant create view to scott;
--将以下4个动态性能视图原表SELECT权限赋给scott
grant SELECT on v_$statname to scott ;
grant SELECT on v_$mystat to scott ;
grant SELECT on v_$latch to scott ;
grant SELECT on v_$timer to scott ;
scott用户下登录，执行以下语句
--创建统计结果表
create or replace view stats
as select 'STAT...' || a.name name, b.value
from sys.v_$statname a, sys.v_$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from sys.v_$latch
union all
select 'STAT...Elapsed Time', hsecs from sys.v_$timer; --创建临时表收集统计结果
create global temporary table run_stats
(runid varchar2(15),
name varchar2(80),
value int)
on commit preserve rows; --创建runstats包
-- runstats包含3个API，runstats测试开始时调用rs_start，rs_middle会在测试中调用，完成时调用rs_stop，打印报告
-- rs_stop的p_difference_threshold参数，用来控制最后打印的数据量，输入这个参数可只查看差值大于参数的统计结果和闩信息，默认为0全部显示 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; --打印每次运行累计CPU时间，分别打印两次运行的统计结果和闩值（只打印超过p_difference_threshold的结果）
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;
/
工具创建好了之后，可以拿个例子来测试一下，把下面语句写入test.sql，做成一个SQL文件,sqlplus中执行
drop table testStat;
create table testStat(id varchar2(10));
exec runstats_pkg.rs_start;
exec dbms_output.put_line('rs_start....');
insert into testStat select level from dual connect by level <=500000;
commit;
exec dbms_output.put_line('insert completed....');
exec runstats_pkg.rs_middle;
exec dbms_output.put_line('rs_middle....');
begin
for i in 1 .. 500000
loop
insert into testStat values (i);
end loop;
commit;
end;
/
exec dbms_output.put_line('loop insert....');
exec runstats_pkg.rs_stop(0);
结果如下：
[oracle@RHEL65 test]$ sqlplus scott/oracle@orcl @t.sql SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 30 16:54:09 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Table dropped. Table created. PL/SQL procedure successfully completed. rs_start.... PL/SQL procedure successfully completed. 500000 rows created. Commit complete. insert completed.... PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. rs_middle.... PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. loop insert.... PL/SQL procedure successfully completed. Run1 ran in 66 cpu hsecs
Run2 ran in 2217 cpu hsecs
run 1 ran in 2.98% of the time Name Run1 Run2 Diff
STAT...opened cursors current -1 0 1
STAT...redo synch writes 2 1 -1
STAT...commit txn count during 2 3 1
STAT...IMU Flushes 2 1 -1
STAT...rows fetched via callba 5 4 -1
STAT...cursor authentications 0 1 1
STAT...buffer is pinned count 1 2 1
STAT...parse time elapsed 1 0 -1
LATCH.channel handle pool latc 2 1 -1
LATCH.queued dump request 0 1 1
LATCH.MinActiveScn Latch 0 1 1
LATCH.Shared B-Tree 1 2 1
LATCH.hash table modification 1 0 -1
LATCH.SQL memory manager latch 0 1 1
LATCH.kwqbsn:qsga 0 1 1
LATCH.threshold alerts latch 0 1 1
STAT...IMU pool not allocated 0 2 2
STAT...IMU- failed to get a pr 0 2 2
STAT...SQL*Net roundtrips to/f 10 8 -2
LATCH.ksuosstats global area 0 2 2
LATCH.dml lock allocation 2 4 2
STAT...user calls 15 12 -3
STAT...sorts (memory) 11 8 -3
STAT...sorts (rows) 5 2 -3
LATCH.object stats modificatio 7 4 -3
LATCH.kcbtsemkid latch 0 3 3
LATCH.managed standby latch 0 3 3
LATCH.parameter list 0 3 3
LATCH.session state list latch 3 0 -3
LATCH.session switching 1 5 4
LATCH.ksv allocation latch 0 4 4
LATCH.sort extent pool 0 4 4
LATCH.deferred cleanup latch 0 4 4
LATCH.cp sga latch 0 4 4
LATCH.parallel query alloc buf 1 5 4
LATCH.ncodef allocation latch 0 4 4
LATCH.qmn task queue latch 0 4 4
LATCH.ASM network state latch 0 4 4
STAT...write clones created in 0 5 5
STAT...immediate (CURRENT) blo 14 9 -5
LATCH.resmgr:active threads 0 5 5
LATCH.resmgr:schema config 0 5 5
LATCH.job_queue_processes para 0 5 5
STAT...table scans (short tabl 6 12 6
STAT...table scan blocks gotte 4 10 6
LATCH.FAL Queue 0 6 6
LATCH.alert log latch 0 6 6
LATCH.reservation so alloc lat 0 6 6
LATCH.transaction allocation 15 8 -7
LATCH.OS process allocation 0 9 9
LATCH.KMG MMAN ready and start 0 9 9
LATCH.Change Notification Hash 0 9 9
LATCH.Real-time plan statistic 0 9 9
STAT...redo buffer allocation 0 10 10
STAT...physical read total IO 2 13 11
STAT...physical reads 2 13 11
STAT...physical reads cache 2 13 11
STAT...physical read IO reques 2 13 11
LATCH.cache buffer handles 1,064 1,076 12
LATCH.archive control 0 12 12
LATCH.Reserved Space Latch 0 12 12
LATCH.session timer 0 12 12
LATCH.kks stats 1 15 14
LATCH.shared pool simulator 10 24 14
STAT...Heap Segment Array Upda 23 8 -15
STAT...switch current to new b 31 14 -17
STAT...calls to get snapshot s 188 171 -17
STAT...cluster key scans 51 34 -17
STAT...cluster key scan block 51 34 -17
STAT...index scans kdiixs1 88 105 17
STAT...deferred (CURRENT) bloc 32 14 -18
LATCH.FIB s.o chain latch 0 18 18
STAT...consistent changes 63 44 -19
STAT...table fetch by rowid 31 50 19
STAT...shared hash latch upgra 50 70 20
LATCH.archive process latch 0 21 21
LATCH.space background task la 0 21 21
STAT...consistent gets - exami 888 912 24
STAT...index fetch by key 52 28 -24
LATCH.FOB s.o list latch 2 26 24
STAT...commit cleanouts 867 842 -25
STAT...commit cleanouts succes 861 836 -25
STAT...no work - consistent re 146 171 25
STAT...workarea memory allocat -46 -21 25
LATCH.session idle bit 35 63 28
STAT...hot buffers moved to he 0 29 29
LATCH.In memory undo latch 15 45 30
STAT...buffer is not pinned co 277 312 35
STAT...redo log space requests 0 40 40
LATCH.SGA IO buffer pool latch 2 45 43
LATCH.DML lock allocation 139 93 -46
LATCH.post/wait queue 3 51 48
LATCH.active service list 0 51 51
LATCH.file cache latch 46 108 62
STAT...cleanout - number of kt 744 817 73
STAT...active txn count during 743 816 73
LATCH.call allocation 8 82 74
LATCH.active checkpoint queue 9 84 75
LATCH.session allocation 12 115 103
LATCH.ASM db client latch 2 106 104
LATCH.object queue header heap 22 132 110
LATCH.Consistent RBA 15 129 114
LATCH.lgwr LWN SCN 15 129 114
LATCH.mostly latch-free SCN 15 131 116
STAT...table scan rows gotten 50 176 126
LATCH.message pool operations 4 130 126
STAT...enqueue releases 280 409 129
STAT...enqueue requests 280 409 129
STAT...enqueue conversions 3 147 144
LATCH.JS queue state obj latch 0 180 180
STAT...messages sent 14 202 188
STAT...file io wait time 66 276 210
STAT...non-idle wait count 21 251 230
STAT...redo log space wait tim 0 355 355
STAT...IMU undo allocation siz 712 1,080 368
STAT...change write time 3 403 400
LATCH.redo writing 49 486 437
STAT...bytes sent via SQL*Net 2,379 1,894 -485
LATCH.row cache objects 1,062 1,577 515
STAT...non-idle wait time 2 534 532
LATCH.SQL memory manager worka 7 610 603
LATCH.channel operations paren 15 694 679
LATCH.redo allocation 52 751 699
STAT...bytes received via SQL* 3,942 3,228 -714
STAT...calls to kcmgcs 1,795 1,027 -768
STAT...consistent gets from ca 2,060 1,290 -770
STAT...consistent gets 2,060 1,290 -770
STAT...consistent gets from ca 1,087 287 -800
STAT...db block gets from cach 2,252 1,216 -1,036
LATCH.messages 63 1,136 1,073
LATCH.enqueues 348 1,422 1,074
LATCH.enqueue hash chains 566 1,722 1,156
STAT...recursive cpu usage 5 1,912 1,907
STAT...CPU used when call star 66 2,217 2,151
STAT...CPU used by this sessio 66 2,217 2,151
STAT...Elapsed Time 68 2,751 2,683
STAT...DB time 67 2,751 2,684
STAT...Heap Segment Array Inse 2,779 14 -2,765
STAT...free buffer requested 1,063 5,148 4,085
STAT...redo subscn max counts 1,021 5,124 4,103
STAT...calls to kcmgas 300 4,409 4,109
STAT...redo ordering marks 233 4,369 4,136
LATCH.simulator lru latch 114 4,520 4,406
LATCH.undo global data 1,065 5,517 4,452
STAT...free buffer inspected 0 4,479 4,479
LATCH.cache buffers lru chain 187 5,401 5,214
LATCH.checkpoint queue latch 221 6,512 6,291
STAT...IMU Redo allocation siz 540 11,212 10,672
LATCH.object queue header oper 2,563 25,027 22,464
LATCH.simulator hash latch 949 36,561 35,612
STAT...session cursor cache hi 78 41,324 41,246
STAT...physical read bytes 16,384 106,496 90,112
STAT...cell physical IO interc 16,384 106,496 90,112
STAT...physical read total byt 16,384 106,496 90,112
STAT...HSC Heap Segment Block 2,813 500,029 497,216
STAT...redo entries 6,853 504,649 497,796
STAT...recursive calls 1,443 501,196 499,753
STAT...execute count 172 500,170 499,998
STAT...opened cursors cumulati 159 500,162 500,003
LATCH.shared pool 219 500,325 500,106
STAT...session logical reads 12,488 516,953 504,465
STAT...db block gets 10,428 515,663 505,235
STAT...db block gets from cach 10,428 515,663 505,235
STAT...session pga memory 524,288 -196,608 -720,896
STAT...session uga memory 785,856 -196,464 -982,320
STAT...db block changes 10,145 1,009,164 999,019
LATCH.cache buffers chains 41,525 2,576,396 2,534,871
STAT...session pga memory max 24,772,608 0 -24,772,608
STAT...session uga memory max 24,843,448 0 -24,843,448
STAT...undo change vector size 1,210,048 34,006,444 32,796,396
STAT...redo size 8,857,832 124,592,660 115,734,828 Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
50,679 3,171,932 3,121,253 1.60% PL/SQL procedure successfully completed. scott@ORCL>