-- EITHER...
--   grant SELECT on V_$STATNAME to a521151;
--   grant SELECT on V_$MYSTAT   to a521151;
--   grant SELECT on V_$TIMER    to a521151;
--   grant SELECT on V_$LATCH    to a521151;
-- OR...
--   create view STATS
--   grant SELECT on STATS       to <me>

create or replace view STATS as
  select 'STAT...'||a.name name, b.value from v$STATNAME a, v$MYSTAT b
   where a.statistic# = b.statistic#
  union all
  select 'LATCH.'||name,         gets    from v$LATCH
  union all
  select 'STAT...Elapsed Time',  hsecs   from v$TIMER;

create global temporary table RUN_STATS
 (runid varchar2(15),
  name varchar2(80),
  value int)
  on commit preserve rows;

create or replace package RUNSTATS_PKG
as
  procedure RS_START;
  procedure RS_MIDDLE;
  procedure RS_STOP(p_difference_threshold in number default 0);
end;
/

create or replace package body RUNSTATS_PKG
as
  g_start number;
  g_run1  number;
  g_run2  number;
procedure RS_START
is
  begin
    delete from RUN_STATS;
    insert into RUN_STATS  select 'before',  STATS.* from STATS;
    g_start := dbms_utility.get_cpu_time;
  end;
procedure RS_MIDDLE
is
  begin
    g_run1 := (dbms_utility.get_cpu_time-g_start);
    insert into RUN_STATS  select 'after 1', STATS.* from STATS;
    g_start := dbms_utility.get_cpu_time;
  end;
procedure RS_STOP(p_difference_threshold in number default 0)
is
  begin
    g_run2 := (dbms_utility.get_cpu_time-g_start);
    insert into RUN_STATS  select 'after 2', STATS.* from STATS;

    dbms_output.put_line( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
    dbms_output.put_line( 'Run2 ran in ' || g_run2 || ' cpu 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) );
    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' ) data
          from RUN_STATS a, RUN_STATS b, RUN_STATS c
         where a.name = b.name     and b.name = c.name
           and 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_threshold
         order by abs( (c.value-b.value)-(b.value-a.value))
      )
    loop
      dbms_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' ) || '%' data
          from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
                        sum( (c.value-b.value)-(b.value-a.value)) diff
                   from RUN_STATS a, RUN_STATS b, RUN_STATS c
                  where a.name = b.name    and b.name = c.name
                    and a.runid = 'before' and b.runid = 'after 1'  and c.runid = 'after 2'
                    and a.name like 'LATCH%'
                  )
      )
    loop
      dbms_output.put_line( x.data );
    end loop;
  end;
end;
 /
