About
Runstats is a tool that Tom Kyte have developed to compare two different methods of doing the same thing and show which one is superior. You supply the two different methods and runstats does the rest.
Runstats simply measures three key things :
- Wall clock or elapsed time. This is useful to know, but it isn't the most important piece of information
- System statistics: this shows, side by side, how many times each approach did something (e.g. a parse call) and the difference between the two
- Latching. This is the key output of this report when developing an application.
The package runstats runstats_pkg contains three simple API calls :
- RS_START (runstats start) to be called at the beginning of a runstats test
- RS_MIDDLE to be called in the middle, as you might have guessed
- RS_STOP(p_difference_threshold number) to finish off and print the report. The parameter p_difference_threshold is used to control the amount of data printed at the end.
Runstats collects statistics and latching information for each run, and then prints a report of how much of a resource each test (each approach) used and the difference between them.
Articles Related
Example
This example show how bind variable are scalable and minimize the number of latches.
declare
procedure method1 ( p_data in varchar2 )
is
begin
execute immediate 'insert into t(x) values (:x)'
using p_data;
end method1;
procedure method2 ( p_data in varchar2 )
is
begin
execute immediate 'insert into t(x) values (''' || replace( p_data, '''', '''''' ) || ''' )';
end method2;
begin
runstats_pkg.rs_start;
for i in 1 .. 10000
loop
method1 ( 'row ' || I );
end loop;
runstats_pkg.rs_middle;
for i in 1 .. 10000
loop
method2 ( 'row ' || I );
end loop;
runstats_pkg.rs_stop;
end;
/
Result :
Run1 ran in 33 hsecs
Run2 ran in 279 hsecs
run 1 ran in 11.83% of the time
Name Run1 Run2 Diff
LATCH.PL/SQL warning settings 0 1 1
LATCH.transaction allocation 0 1 1
LATCH.OS process allocation 0 1 1
LATCH.list of block allocation 1 2 1
LATCH.KWQMN job cache list lat 0 1 1
LATCH.kokc descriptor allocati 0 2 2
LATCH.session timer 0 2 2
LATCH.buffer pool 0 2 2
LATCH.KMG resize request state 0 2 2
LATCH.ASM db client latch 0 2 2
LATCH.parameter table allocati 0 2 2
LATCH.transaction branch alloc 0 2 2
LATCH.post/wait queue 0 2 2
LATCH.object stats modificatio 0 2 2
LATCH.parameter list 0 3 3
LATCH.file cache latch 0 3 3
LATCH.FIB s.o chain latch 0 4 4
LATCH.active checkpoint queue 0 5 5
LATCH.session state list latch 6 0 -6
LATCH.FOB s.o list latch 0 6 6
LATCH.redo writing 6 13 7
LATCH.object queue header heap 0 7 7
LATCH.session idle bit 1 10 9
LATCH.dml lock allocation 1 10 9
LATCH.active service list 0 11 11
LATCH.library cache pin alloca 0 13 13
LATCH.internal temp table obje 0 16 16
LATCH.cache buffer handles 0 16 16
LATCH.channel operations paren 0 17 17
LATCH.library cache lock alloc 0 23 23
LATCH.undo global data 22 50 28
LATCH.In memory undo latch 3 33 30
LATCH.redo allocation 45 9 -36
LATCH.JS queue state obj latch 0 36 36
LATCH.simulator lru latch 0 37 37
LATCH.checkpoint queue latch 0 54 54
LATCH.object queue header oper 185 301 116
LATCH.simulator hash latch 593 727 134
LATCH.KMG MMAN ready and start 0 169 169
LATCH.Memory Management Latch 0 187 187
LATCH.library cache load lock 0 210 210
LATCH.messages 16 380 364
LATCH.shared pool sim alloc 0 448 448
LATCH.cache buffers lru chain 88 691 603
LATCH.enqueues 7 20,416 20,409
LATCH.enqueue hash chains 11 20,425 20,414
LATCH.session allocation 1 36,938 36,937
LATCH.kks stats 0 58,615 58,615
LATCH.library cache pin 4 60,685 60,681
....
LATCH.library cache lock 8 61,564 61,556
LATCH.cache buffers chains 50,907 118,493 67,586
LATCH.row cache objects 12 126,049 126,037
LATCH.library cache 14 256,344 256,330
LATCH.shared pool simulator 1 303,372 303,371
LATCH.shared pool 0 338,271 338,271
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
51,944 1,404,820 1,352,876 3.70%
PL/SQL procedure successfully completed.
gerardnico@ORCL>