Oracle Database - Runstats

> Database > Oracle Database

1 - 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.

3 - 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.

[email protected]>
Advertising

4 - Reference

db/oracle/runstats.txt · Last modified: 2017/10/27 15:54 by gerardnico