Runstats

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.

What is Runstats?

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.

Installation

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:

• A global temporary table called run_stats
• A view called stats
• A plsql package called runstats_pkg

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.

Usage

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.