本文介绍了一款名为runstats的Oracle基准测试工具，详细展示了如何通过一系列步骤配置环境并使用该工具进行性能测试，包括视图创建、临时表设置、测试包定义等。
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
Run2 ran in 744 cpu hsecs
Run 1 ran in .27 % of the time
Name Run1 Run2 Diff
LATCH.job_queue_processes para 0 1 1
LATCH.ncodef allocation latch 0 1 1
LATCH.kwqbsn:qsga 1 0 -1
LATCH.threshold alerts latch 1 0 -1
LATCH.active checkpoint queue 5 4 -1
LATCH.transaction branch alloc 0 1 1
LATCH.sort extent pool 0 1 1
LATCH.resmgr:actses change gro 0 1 1
LATCH.Shared B-Tree 1 0 -1
STAT..parse count (failures) 1 0 -1
LATCH.session switching 0 1 1
LATCH.ksuosstats global area 0 1 1
LATCH.event group latch 0 1 1
LATCH.FOB s.o list latch 0 1 1
STAT..rows fetched via callbac 0 1 1
STAT..session cursor cache cou 0 -1 -1
LATCH.ncodef allocation latch 0 1 1
LATCH.kwqbsn:qsga 0 1 1
LATCH.threshold alerts latch 0 1 1
STAT..messages sent 1 0 -1
LATCH.transaction branch alloc 0 1 1
LATCH.sort extent pool 0 1 1
LATCH.resmgr:actses change gro 0 1 1
LATCH.Shared B-Tree 0 1 1
LATCH.job_queue_processes para 0 1 1
LATCH.session switching 0 1 1
LATCH.ksuosstats global area 0 1 1
LATCH.event group latch 0 1 1
LATCH.FOB s.o list latch 0 1 1
LATCH.active checkpoint queue 4 5 1
STAT..rows fetched via callbac 0 1 1
STAT..session cursor cache cou 0 -1 -1
STAT..parse count (failures) 0 1 1
STAT..messages sent 1 0 -1
LATCH.session timer 2 4 2
LATCH.KMG MMAN ready and start 2 4 2
LATCH.checkpoint queue latch 68 70 2
LATCH.list of block allocation 1 3 2
LATCH.transaction allocation 0 2 2
LATCH.user lock 0 2 2
LATCH.post/wait queue 4 2 -2
LATCH.process allocation 0 2 2
LATCH.process group creation 0 2 2
LATCH.parameter table allocati 0 2 2
LATCH.channel handle pool latc 0 2 2
LATCH.OS process: request allo 0 2 2
STAT..cursor authentications 2 0 -2
STAT..redo buffer allocation r 2 0 -2
STAT..redo log space requests 2 0 -2
LATCH.list of block allocation 1 3 2
LATCH.transaction allocation 0 2 2
LATCH.user lock 0 2 2
LATCH.post/wait queue 4 2 -2
LATCH.process allocation 0 2 2
LATCH.process group creation 0 2 2
LATCH.parameter table allocati 0 2 2
LATCH.channel handle pool latc 0 2 2
LATCH.OS process: request allo 0 2 2
STAT..redo buffer allocation r 2 0 -2
STAT..redo log space requests 2 0 -2
LATCH.JS slv state obj latch 0 3 3
LATCH.mostly latch-free SCN 3 6 3
LATCH.lgwr LWN SCN 3 6 3
LATCH.Consistent RBA 3 6 3
LATCH.JS slv state obj latch 0 3 3
LATCH.parallel query alloc buf 4 0 -4
LATCH.compile environment latc 5 9 4
LATCH.session state list latch 0 4 4
LATCH.qmn task queue latch 4 0 -4
LATCH.PL/SQL warning settings 13 9 -4
LATCH.cache buffers lru chain 18 22 4
LATCH.dummy allocation 0 4 4
LATCH.resmgr:actses active lis 0 4 4
LATCH.resmgr:free threads list 0 4 4
STAT..buffer is pinned count 0 4 4
LATCH.session state list latch 0 4 4
LATCH.qmn task queue latch 0 4 4
LATCH.dummy allocation 0 4 4
LATCH.resmgr:actses active lis 0 4 4
LATCH.resmgr:free threads list 0 4 4
LATCH.session timer 1 5 4
LATCH.parallel query alloc buf 0 4 4
LATCH.compile environment latc 5 9 4
LATCH.KMG MMAN ready and start 1 5 4
LATCH.cache buffers lru chain 18 22 4
STAT..heap block compress 6 10 4
STAT..buffer is pinned count 0 4 4
LATCH.object queue header oper 130 135 5
LATCH.redo writing 23 28 5
LATCH.resmgr group change latc 0 5 5
STAT..active txn count during 25 20 -5
STAT..cleanout - number of ktu 25 20 -5
STAT..calls to kcmgcs 25 20 -5
LATCH.resmgr group change latc 0 5 5
LATCH.Consistent RBA 2 7 5
STAT..calls to kcmgcs 20 25 5
STAT..active txn count during 20 25 5
STAT..cleanout - number of ktu 20 25 5
STAT..SQL*Net roundtrips to/fr 12 6 -6
STAT..heap block compress 11 5 -6
STAT..SQL*Net roundtrips to/fr 6 12 6
STAT..workarea executions - op 13 20 7
LATCH.OS process allocation 3 10 7
LATCH.mostly latch-free SCN 1 8 7
LATCH.lgwr LWN SCN 1 8 7
LATCH.object queue header oper 129 136 7
LATCH.session idle bit 49 57 8
STAT..workarea memory allocate -1 7 8
LATCH.PL/SQL warning settings 7 15 8
STAT..redo entries 10,108 10,116 8
STAT..workarea memory allocate -1 7 8
LATCH.OS process 0 9 9
LATCH.OS process allocation 2 11 9
LATCH.channel operations paren 55 64 9
LATCH.OS process 0 9 9
STAT..user calls 10 20 10
STAT..change write time 2 12 10
STAT..redo entries 10,117 10,107 -10
STAT..user calls 20 10 -10
STAT..change write time 2 12 10
LATCH.active service list 18 30 12
STAT..shared hash latch upgrad 4 16 12
STAT..index scans kdiixs1 4 16 12
STAT..redo log space wait time 12 0 -12
STAT..shared hash latch upgrad 4 16 12
STAT..redo log space wait time 12 0 -12
STAT..index scans kdiixs1 4 16 12
LATCH.library cache pin alloca 0 13 13
LATCH.library cache pin alloca 0 13 13
LATCH.redo allocation 71 55 -16
STAT..table fetch by rowid 0 19 19
STAT..workarea executions - op 7 26 19
STAT..consistent changes 40 21 -19
LATCH.undo global data 40 59 19
STAT..table fetch by rowid 0 19 19
STAT..consistent changes 21 40 19
LATCH.library cache lock alloc 3 24 21
LATCH.redo writing 15 36 21
LATCH.library cache lock alloc 3 24 21
LATCH.client/application info 0 25 25
LATCH.client/application info 0 25 25
LATCH.messages 60 86 26
STAT..db block changes 20,258 20,230 -28
STAT..db block changes 20,230 20,258 28
LATCH.active service list 10 38 28
LATCH.undo global data 35 64 29
STAT..cluster key scans 4 37 33
LATCH.In memory undo latch 10 43 33
STAT..cluster key scans 4 37 33
LATCH.redo allocation 82 44 -38
STAT..index fetch by key 5 44 39
STAT..index fetch by key 5 44 39
STAT..sorts (memory) 7 47 40
STAT..cluster key scan block g 6 47 41
STAT..cluster key scan block g 6 47 41
STAT..sorts (memory) 6 48 42
STAT..session cursor cache hit 10 55 45
LATCH.dml lock allocation 21 66 45
LATCH.In memory undo latch 2 51 49
STAT..execute count 10,024 10,074 50
LATCH.session idle bit 27 79 52
STAT..session cursor cache hit 6 59 53
STAT..opened cursors cumulativ 27 83 56
STAT..no work - consistent rea 8 67 59
STAT..no work - consistent rea 8 67 59
STAT..execute count 10,018 10,080 62
LATCH.channel operations paren 28 91 63
LATCH.library cache load lock 8 74 66
LATCH.library cache load lock 8 74 66
STAT..opened cursors cumulativ 21 89 68
LATCH.JS queue state obj latch 36 108 72
STAT..buffer is not pinned cou 6 81 75
STAT..buffer is not pinned cou 6 81 75
LATCH.checkpoint queue latch 31 107 76
LATCH.dml lock allocation 5 82 77
LATCH.messages 34 112 78
STAT..consistent gets - examin 40 126 86
STAT..consistent gets - examin 35 131 96
STAT...Elapsed time 819 1,087 268
LATCH.SQL memory manager worka 75 355 280
STAT..parse time elapsed 5 636 631
STAT..parse time cpu 2 639 637
STAT..parse time elapsed 0 641 641
STAT..parse time cpu 0 641 641
STAT..recursive cpu usage 40 717 677
STAT..DB time 65 745 680
STAT..recursive cpu usage 35 722 687
STAT..CPU used when call start 49 745 696
STAT..DB time 57 753 696
STAT..CPU used by this session 47 751 704
STAT..CPU used when call start 44 750 706
STAT..CPU used by this session 42 756 714
LATCH.simulator lru latch 7 727 720
LATCH.simulator hash latch 10 730 720
LATCH.simulator lru latch 5 729 724
LATCH.simulator hash latch 8 732 724
STAT...Elapsed time 398 1,508 1,110
STAT..bytes sent via SQL*Net t 2,120 882 -1,238
STAT..bytes sent via SQL*Net t 882 2,120 1,238
STAT..bytes received via SQL*N 2,807 1,426 -1,381
STAT..bytes received via SQL*N 1,393 2,840 1,447
LATCH.session allocation 236 1,979 1,743
LATCH.session allocation 236 1,979 1,743
STAT..undo change vector size 643,096 645,148 2,052
STAT..undo change vector size 645,156 643,088 -2,068
STAT..sorts (rows) 4,685 2,421 -2,264
STAT..sorts (rows) 2,343 4,763 2,420
STAT..redo size 2,378,192 2,380,624 2,432
STAT..redo size 2,380,900 2,377,916 -2,984
STAT..enqueue releases 19 10,024 10,005
STAT..enqueue requests 20 10,025 10,005
STAT..parse count (hard) 4 10,010 10,006
STAT..enqueue releases 18 10,025 10,007
STAT..enqueue requests 19 10,026 10,007
STAT..parse count (hard) 3 10,011 10,008
STAT..parse count (total) 26 10,043 10,017
STAT..parse count (total) 19 10,050 10,031
STAT..calls to get snapshot sc 32 10,084 10,052
STAT..calls to get snapshot sc 31 10,085 10,054
STAT..consistent gets 85 10,233 10,148
STAT..consistent gets from cac 85 10,233 10,148
STAT..consistent gets from cac 74 10,244 10,170
STAT..consistent gets 74 10,244 10,170
STAT..recursive calls 10,193 20,871 10,678
STAT..recursive calls 10,191 20,873 10,682
STAT..db block gets from cache 10,385 30,341 19,956
STAT..db block gets 10,385 30,341 19,956
STAT..db block gets from cache 10,362 30,364 20,002
STAT..db block gets 10,362 30,364 20,002
LATCH.enqueues 197 20,222 20,025
LATCH.enqueue hash chains 221 20,295 20,074
LATCH.enqueues 115 20,304 20,189
LATCH.enqueue hash chains 122 20,394 20,272
STAT..session logical reads 10,470 40,574 30,104
STAT..session logical reads 10,436 40,608 30,172
LATCH.kks stats 6 37,782 37,776
LATCH.kks stats 6 37,782 37,776
LATCH.library cache pin 20,337 70,613 50,276
LATCH.library cache pin 20,271 70,679 50,408
LATCH.library cache lock 239 60,453 60,214
LATCH.library cache lock 221 60,471 60,250
LATCH.cache buffers chains 51,373 113,607 62,234
LATCH.cache buffers chains 51,251 113,729 62,478
LATCH.row cache objects 151 120,611 120,460
LATCH.row cache objects 133 120,629 120,496
LATCH.library cache 20,574 234,325 213,751
LATCH.library cache 20,473 234,426 213,953
LATCH.shared pool 10,155 229,543 219,388
LATCH.shared pool 10,111 229,587 219,476
STAT..session pga memory 0 262,144 262,144
STAT..session pga memory 0 262,144 262,144
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
207,967 1,825,217 1,617,250 11.39%
Total execution time 0.219 sec.
ps:runstats_pkg.rs_stop不带参数会输出全部讯息，也可带参数(参数表示前后差异数超过的数目)
eg. runstats_pkg.rs_stop(1000)表示前后差异数超过1000的讯息(不包含1000)。
扫一扫
2857
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
