Performance Comparison tool runstats

Runstats can compare two different methods for the same thing to find out who is better. We only need to provide two different methods, and runstats is responsible for the rest of the tasks. Runstats is only a measurement of three elements:

1. Time consumed.

2. system statistics: displays the number of times each method performs something (such as executing a resolution call) side by side, and shows the difference between the two.

3. latching, which is the key output of the report.

Installation Process:

Create global temporary table run_stats

(  runid varchar2(15),   name varchar2(80),   value int )on commit preserve rows;create or replace view stats as select 'STAT...' || a.name name, b.value   from v$statname a, v$mystat bwhere a.statistic# = b.statistic#union all  select 'LATCH.' || name, gets from v$latchunion all  select 'STAT...Elapsed Time', hsecs from v$timer;    create or replace package runstats_pkgasprocedure rs_start;procedure rs_middle;procedure rs_stop( p_difference_threshold in number default 0 );end;/create or replace package body runstats_pkgas  g_start number;  g_run1 number;  g_run2 number;procedure rs_startisbegin  delete from run_stats;  insert into run_stats select 'before', stats.* from stats;  g_start := dbms_utility.get_time;end;procedure rs_middleisbegin  g_run1 := (dbms_utility.get_time-g_start);  insert into run_stats  select 'after 1', stats.* from stats;  g_start := dbms_utility.get_time;end;procedure rs_stop(p_difference_threshold in number default 0)isbegin g_run2 := (dbms_utility.get_time-g_start); dbms_output.put_line ( 'Run1 ran in ' || g_run1 || ' hsecs' ); dbms_output.put_line ( 'Run2 ran in ' || g_run2 || ' hsecs' );if ( g_run2 <> 0 ) then dbms_output.put_line ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||'% of the time' );end if;dbms_output.put_line( chr(9) );insert into run_stats select 'after 2', stats.* from stats;dbms_output.put_line( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );for x in( select rpad( a.name, 30 ) ||to_char( b.value-a.value, '999,999,999' ) ||to_char( c.value-b.value, '999,999,999' ) ||to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) datafrom run_stats a, run_stats b, run_stats cwhere a.name = b.nameand b.name = c.nameand a.runid = 'before'and b.runid = 'after 1'and c.runid = 'after 2'and abs( (c.value-b.value) - (b.value-a.value) )> p_difference_thresholdorder by abs( (c.value-b.value)-(b.value-a.value))) loopdbms_output.put_line( x.data );end loop;dbms_output.put_line( chr(9) );dbms_output.put_line( 'Run1 latches total versus runs -- difference and pct' );dbms_output.put_line( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );for x in( select to_char( run1, '999,999,999' ) ||to_char( run2, '999,999,999' ) ||to_char( diff, '999,999,999' ) ||to_char( round( run1/decode( run2, 0, to_number(0), run2) *100,2 ), '99,999.99' ) || '%' datafrom ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2, sum( (c.value-b.value)-(b.value-a.value)) difffrom run_stats a, run_stats b, run_stats cwhere a.name = b.nameand b.name = c.nameand a.runid = 'before'and b.runid = 'after 1'and c.runid = 'after 2'and a.name like 'LATCH%')) loopdbms_output.put_line( x.data );end loop;end;end;/ 

Test design, test and comparison without binding variables:

-- SQL for the test process

Drop table t purge; create table t (x int); -- no Bind Variable create or replace procedure p1as l_cnt number; beginfor I in 1 .. 10000 loop execute immediate 'select count (*) from t where x = '| I into l_cnt; end loop; end;/-- bind the variable create or replace procedure p2as l_cnt number; beginfor I in 1 .. 10000 loop select count (*) into l_cnt from t where x = I; end loop; end;/exec runStats_pkg.rs_start; exec p1; exec runStats_pkg.rs_middle; set serveroutput on; exec p2; exec runstats_pkg.rs_stopped (1000 );

Test results:

Run1 ran in 311 hsecs
Run2 ran in 30 hsecs
Run 1 ran in 1036.67% of the time

Name Run1 Run2 Diff
STAT... bytes sent via SQL * Net 276 1,524 1,248
LATCH. session allocation 1,695 74-1,621
STAT... parse count (hard) 10,011 5-10,006
STAT... enqueue releases 10,016 8-10,008
STAT... enqueue requests 10,017 8-10,009
STAT... parse count (total) 10,041 14-10,027
STAT... callto get snapshot s 50,073 40,020-10,053
STAT... consistent gets 40,194 30,060-10,134
STAT... consistent gets from ca 40,194 30,060-10,134
STAT... session logical reads 40,232 30,087-10,145
STAT... recursive cballs 20,920 10,076-10,844
LATCH. enqueue hash chains 20,117 16-20,101
LATCH. enqueues 20,124 18-20,106
LATCH. cache buffers chains 80,492 60,209-20,283
STAT... physical read total byt 0 24,576 24,576
STAT .. physical read bytes 0 24,576 24,576
LATCH. kks stats 25,415 10-25,405
LATCH. library cache pin 70,385 20,185-50,200
STAT... session uga memory 65,560 7,488-58,072
LATCH. library cache lock 60,292 179-60,113
STAT... session pga memory 65,536 0-65,536
LATCH. row cache objects 150,201 356-149,845
LATCH. shared pool 206,050 10,230-195,820
STAT... session uga memory max 261,964 65,560-196,404
LATCH. library cache 216,965 20,474-196,491
STAT... session pga memory max 262,144 65,536-196,608

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
854,415 111,834-742,581 764.00%