效率测试小工具runstats学习及应用示例
效率测试小工具runstats学习及应用示例
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)。
评论暂时关闭