Wall clock time like that is useful to a degree *in a single user system* with no wait events (all buffered IO, you own the CPU, no waiting on disk to through things off). In your case, you probably have a large enough buffer cache, you did not hit physical reads -- it was all about CPU. If you look at my example, the first query (colocated) did 258 physical reads, the second -- 1,684 (buffer cache not big enough to hold it all)
What you have to take into consideration as well are the other hidden scalability factors. Each consistent get will UP the number of latches we get to process this query. A latch = lock, lock = serialization device, serialization device = less scalable.
Using the technique outlined on:
</code>
http://asktom.oracle.com/~tkyte/runstats.html <code>
I ran this test:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table colocated ( x int, y varchar2(2000) ) pctfree 0;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated values ( i, rpad(dbms_random.random,75,'*') );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table disorganized nologging pctfree 0
2 as
3 select x, y from colocated ORDER BY y
4 /
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table colocated add constraint colocated_pk primary key(x);
Table altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table disorganized add constraint disorganized_pk primary key(x);
Table altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table run_stats;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;
View created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3
4 type Tab is table of varchar2(255);
5 l_x Tab;
6 l_y Tab;
7 begin
8 insert into run_stats select 'before', stats.* from stats;
9
10 l_start := dbms_utility.get_time;
11 select x, y bulk collect into l_x, l_y from COLOCATED where x between 20000 and 40000;
12 dbms_output.put_line( l_x.count || ' rows fetched from colocated' );
13 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
14
15
16 insert into run_stats select 'after 1', stats.* from stats;
17 l_start := dbms_utility.get_time;
18 select x, y bulk collect into l_x, l_y from DISORGANIZED where x between 20000 and 40000;
19 dbms_output.put_line( l_x.count || ' rows fetched from disorganized' );
20 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
21
22 insert into run_stats select 'after 2', stats.* from stats;
23 end;
24 /
20001 rows fetched from colocated
45 hsecs
20001 rows fetched from disorganized
166 hsecs
PL/SQL procedure successfully completed.
showing that the wall clock is better for colocated (using RBO here, both used index plans, that can be confirmed by tkprof)
Now, more importantly, lets look at the nitty gritty details:ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
------------------------------ ---------- ---------- ----------
...
STAT...free buffer requested 261 8243 7982
STAT...physical reads 257 8241 7984
LATCH.cache buffers lru chain 260 8302 8042
STAT...session pga memory 0 8512 8512
STAT...session logical reads 295 20055 19760
STAT...buffer is not pinned co 231 19999 19768
unt
STAT...buffer is pinned count 39787 20019 -19768
STAT...no work - consistent re 264 20032 19768
ad gets
STAT...consistent gets 269 20038 19769
LATCH.cache buffers chains 871 48482 47611
39 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
Lots more Latches. Latches in a single user system are not worrisome. Add a user and they drop your speed like a falling brick.