extremely performance difference on two boxes

From: Kenny Yu <kyu_at_biodiscovery.com>
Date: Mon, 21 Apr 2003 13:07:00 -0700
Message-ID: <va8jkk8m1thnbf@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 - 15:07:00 CDT