Re: extremely performance difference on two boxes

"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.
>

Looks like you've found the magic of SGA buffering. Your slow box is reading the data from disk, and your fast box is reading it from a buffer that had previously read it. Look at how much memory is being used on each box, and what the db_block_buffers, as well as what else is running.

jg

--
@home.com is bogus.
"I regard self-sacrifice as a destructive and immoral act." - Peter
Schwartz
Chairman of the board,
Ayn Rand Institute