A collection of tutorials, code and tools to help you get better using Oracle
12 June 2011
Download Runstats here. This code was not written by me, but by Tom Kyte. I have reproduced it here as the last time I tried to find it over on AskTom I couldn't track it down.
Runstats is a fairly simple utility that measures how many latches a piece of code uses in the database, and allows a developer to compare two approaches for efficiency and hence scalability. As a latch is a light weight lock, and locks are serialization devices, code which uses less latches is going to be more scalable.
Download the file linked above and save it somewhere as 'runstats.sql'. Logon to SQLPLUS and run the file:
SQL> @runstats
Table created.
View created.
Package created.
Package body created.
The install will have created three objects:
You will need the following select privileges granted to your user for the install to work:
SQL> grant select on v_$mystat to sodonnel;
Grant succeeded.
SQL> grant select on v_$statname to sodonnel;
Grant succeeded.
SQL> grant select on v_$timer to sodonnel;
Grant succeeded.
SQL> grant select on v_$latch to sodonnel;
Grant succeeded.
The runstats_pkg has three procedures which are used to run a test case.
First set server output on.
Next call rsstart, followed by the first piece of code you want to measure. Next call rsmiddle, followed by the second piece of code you want to test. Finally called rsstop. Upon calling rsstop, a report will be printed that compares the two pieces of code under test. For example:
SQL> set serveroutput on
SQL> create table all_objects_copy
as select owner, object_name, edition_name from all_objects where 1 = 0;
SQL> exec runstats_pkg.rs_start;
SQL> insert into all_objects_copy
select owner, object_name, edition_name from all_objects;
SQL> exec runstats_pkg.rs_middle;
SQL> begin
for row in (select owner, object_name, edition_name
from all_objects) loop
insert into all_objects_copy (owner, object_name, edition_name)
values (row.owner, row.object_name, row.edition_name);
end loop;
end;
/
SQL> runstats_pkg.rs_stop;
Name Run1 Run2 Diff
LATCH.SQL memory manager worka 139,651 139,781 130
STAT...hot buffers moved to he 79 268 189
STAT...bytes received via SQL* 1,018 1,225 207
LATCH.checkpoint queue latch 331 555 224
STAT...buffer is pinned count 16,991 16,715 -276
LATCH.simulator hash latch 1,034 1,456 422
STAT...Elapsed Time 1,127 2,008 881
STAT...consistent gets from ca 12,383 13,316 933
LATCH.row cache objects 19,078 12,745 -6,333
STAT...table scan rows gotten 13,027 19,500 6,473
LATCH.shared pool 665 7,824 7,159
STAT...recursive calls 386 7,892 7,506
STAT...session logical reads 14,491 22,074 7,583
STAT...HSC Heap Segment Block 84 7,697 7,613
STAT...redo entries 283 7,915 7,632
STAT...execute count 36 7,692 7,656
STAT...opened cursors cumulati 35 7,696 7,661
STAT...session cursor cache hi 22 7,687 7,665
STAT...db block gets 528 8,238 7,710
STAT...db block gets from cach 528 8,238 7,710
STAT...db block changes 433 15,738 15,305
LATCH.cache buffers chains 29,596 67,700 38,104
STAT...session pga memory 0 65,536 65,536
STAT...session uga memory 7,488 102,248 94,760
STAT...undo change vector size 26,384 527,424 501,040
STAT...session uga memory max 0 572,680 572,680
STAT...redo size 294,836 2,093,956 1,799,120
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
193,864 233,762 39,898 82.93%
You can tell runstats to only report stats that are over a certain difference threshold by passing the threshold to rs_stop. For example, to only report latches and stats that are over 1000 apart:
SQL> exec runstats_pkg.rs_stop(1000);
In general when the number of latches and stats are smaller for one approach, it indicates it is more efficient.