Runstats.sql This is the test harness I use to try out different ideas. It 
shows two vital sets of statistics for me The elapsed time difference between 
two approaches. It very simply shows me which approach is faster by the wall 
clock How many resources each approach takes. This can be more meaningful then 
even the wall clock timings. For example, if one approach is faster then the 
other but it takes thousands of latches (locks), I might avoid it simply 
because it will not scale as well. The way this test harness works is by saving 
the system statistics and latch information into a temporary table. We then run 
a test and take another snapshot. We run the second test and take yet another 
snapshot. Now we can show the amount of resources used by approach 1 and 
approach 2. 

Requirements 

In order to run this test harness you must at a minimum have: Access to 
V$STATNAME, V$MYSTAT, and V$LATCH If you want to use the view as I have, you 
must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, and 
SYS.V_$LATCH. It will not work to have select on these via a ROLE. You can 
still run the test harness, you just will not be using the view "STATS" I have 
below (substitute in the query text in the PLSQL block where I reference the 
view STATS). The ability to create a table -- run_stats -- to hold the before, 
during and after information. You should note also that the LATCH information 
is collected on a SYSTEM WIDE basis. If you run this on a multi-user system, 
the latch information may be technically "incorrect" as you will count the 
latching information for other sessions - not just your session. This test 
harness works best in a simple, controlled test environment. 

 The table we need is very simple: 
create table run_stats 
( runid varchar2(15), 
  name  varchar2(80), 
  value int 
);
 and if you can get direct grants on the V$ tables necessary (or have your DBA 
create this view and grant SELECT on it to you), you can create this view: 
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;
 Now the test harness itself is very simple. Here it is: 

declare
    l_start number;
    add any other variables you need here for the test... 
begin
    delete from run_stats;
    commit;
    -- start by getting a snapshot of the v$ tables
    insert into run_stats select 'before', stats.* from stats;

    -- and start timing...
    l_start := dbms_utility.get_time;

    -- for things that take a very small amount of time, I like to 
    -- loop over it time and time again, to measure something "big"
    -- if what you are testing takes a long time, loop less or maybe
    -- not at all
    for i in 1 .. 1000
    loop 
        -- your code here for approach #1 
    end loop;

    dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
   
    -- get another snapshot and start timing again... 
    insert into run_stats select 'after 1', stats.* from stats;

    l_start := dbms_utility.get_time;

    for i in 1 .. 1000
    loop 
        -- your code here for approach #2 
    end loop;

    dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
    insert into run_stats select 'after 2', stats.* from stats;
end;
/
 and thats it, now after that block runs, you'll see the wall clock difference 
between the two approaches. You can see the really important stuff using this 
query: 
select a.name, b.value-a.value run1, c.value-b.value run2,
       ( (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 (c.value-a.value) > 0
   and (c.value-b.value) <> (b.value-a.value)
 order by abs( (c.value-b.value)-(b.value-a.value))
/


--=_mixed 0070694688256CFD_Content-Type: application/octet-stream; name="table.sql"
Content-Disposition: attachment; filename="table.sql"
Content-Transfer-Encoding: 7bit

<encoded content removed -- binaries not allowed by ListGuru>