Re: Slow sorts in 8i

In article <37F10726.3DBFECEB_at_hotbot.com>,   James Schrumpf <jschru_at_ncsc.mil> wrote:
> I'm migrating Oracle 7.3.3 tables into an Oracle 8i db, and I've
noticed
> that the 7.3 db is much faster on a sort than the 8i. As a test I'm
> using approx. 40K records with eight mixed CHAR, VARCHAR2, and NUMBER
> columns and ordering on the first column. On the 7.3 db this takes
> about 6 seconds to start returning data, but on the 8i it takes about
> four minutes.
>
> My v$parameters are as follows:
>
> SORT_AREA_SIZE = 65536
> SORT_AREA_RETAINED_SIZE = 65536
> SORT_MULTIBLOCK_READ_COUNT = 16
>
> These are the only v$parameters dealing with sorting that I know of in
> the v$parameters file. The SGA sizes are similar, as are the other
> storage parameters. My db block size is 8K in the 7.3 db and 4K in
the
> 8i.
>
> Any ideas on this problem will certainly be appreciated.
>
> Thanks in advance,
>
> James Schrumpf
> hatteras_at_hotbot.com

Hi.

  Is it possible that you have some indexes on 7.3 that   help you to avoid the sort?
  Check it with EXPLAIN.

  Michael.

  P.S. I made some tests on 7.3.2 and 8.1.5 - the sort

       performance is about the same.

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Sep 30 1999 - 16:44:12 CDT