Re: extremely performance difference on two boxes

On Mon, 21 Apr 2003 13:07:00 -0700, "Kenny Yu" <kyu_at_biodiscovery.com> wrote:

>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
network problem (unlikely, the other query has 2 here)
> 2 sorts (memory)

if plan is the same, how come this one has 2 sorts?
> 1 sorts (disk)

io problem? Could the 'INITIAL' be set very large on TEMP tablespace?
>
>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.
>
>

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Mon Apr 21 2003 - 15:26:58 CDT