效率测试小工具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 statsasselect 'STAT..' || a.name name, b.valuefrom v$statname a, v$mystat bwhere a.statistic# = b.statistic#union allselect 'LATCH.' || name, getsfrom v$latchunion allselect '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_pkgas procedure rs_start; procedure rs_middle; procedure rs_stop( p_difference_threshold in number default 0); --控制打印量，默认输出全部end;/create or replace package body runstats_pkgasg_start number;g_run1 number;g_run2 number;procedure rs_startisbegin delete from run_stats; insert into run_stats select 'before',stats.* from stats; g_start := dbms_utility.get_cpu_time;end;procedure rs_middleisbegin 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)isbegin 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;Beginfor i in 1..10000 Loopinsert into t1 values(i);end loop;end; exec runstats_pkg.rs_middle;Beginfor i in 1..10000 LoopExecute 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)。