A collection of tutorials, code and tools to help you get better using Oracle
12 June 2011
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.