"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.
Are the queries the same? If not, the question doesn't make any sense.
If yes, check execution plans in the trace, note the difference. Post
plans if you can't see the difference for yourself.
Received on Tue Apr 22 2003 - 10:49:53 CDT
