No mystery, just read what you have posted:
> 1164 physical reads (slow)
vs
> 0 physical reads (fast)
and
> 6 SQL*Net roundtrips to/from client (slow)
vs
> 2 SQL*Net roundtrips to/from client (fast)
and
> 1 sorts (disk)(slow)
vs
> 0 sorts (disk) (fast)
Why do you expect more physical IO to be faster than less?
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Kenny Yu" <kyu_at_biodiscovery.com> wrote in message
news:va8jkk8m1thnbf_at_corp.supernews.com...
> I am puzzled at the extremely slow performance on a smaller data set, with
> the same schema
>
> The same select_query runs on one box 100 times slower than the other,
with
> the SAME execution plan. The difference is clear on timing and autotrace
> results.
>
> The Slow one, 30 rows returned
> Oracle 817
> Timing : 5 seconds, with autotrace on or off
> Autotrace stats:
> 8 recursive calls
> 2 db block gets
> 1143 consistent gets
> 1164 physical reads
> 0 redo size
> 6 SQL*Net roundtrips to/from client
> 2 sorts (memory)
> 1 sorts (disk)
>
> The Fast one, 400 rows returned
> Oracle 900
> Timing: 50 ms with autotrace off, 1 seconds with autotrace on
> Autotrace stats:
> 0 recursive calls
> 0 db block gets
> 1616 consistent gets
> 0 physical reads
> 0 redo size
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
>
> On both machines, sql_trace=false, oracle_trace_enable=false. Other init
> parameters are very similar.
>
> Please help.
>
>
Received on Mon Apr 21 2003 - 21:16:00 CDT
