Re: order by / sorting performance issue

Hi James,

Then look at the sort_area_size in your session and if relatively small consider increasing. There will be a threshold at which point there may be little benefit in increasing it further (unless you have sufficient free memory and can increase it sufficiently to avoid a disk sort altogether).

A bit of trial and error and performance could significantly improve.

Regards

Richard
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:ai3cob$tk$1_at_lust.ihug.co.nz...
> A proper locally managed temporary tablespace would be a start. Formed
from
> many datafiles on many hard disks would be better. RAID 0 would be better
> still.
>
> And nothing, but nothing else, on the same hard drives.
>
> Whaddayagot?
>
> Regards
> HJR
>
> "James" <thanatic_at_telstra.com> wrote in message
> news:7377f06c.0207290341.53008811_at_posting.google.com...
> > Hello world,
> >
> > James here.
> >
> > Has anyone got any tips in sorting a wide table?
> > I am very dissatisfied with the present performance and I am looking
> > for some answers.
> >
> > The base table is only about 40000 records and a where clause drives
> > it down to 6000. An unsorted qry returns the result very quickly but
> > an order by (on an unindexed varchar2) is very slow.
> >
> > Now I have added a constraint to this field of "not null" and I have
> > indexed it hoping that the sort operation is not required. But the
> > explain plan still says that it is.
> >
> > I tried to provide a hint but this didn't seem to work either.
> >
> > Is this the way to go in improving my performance? Maybe I have simply
> > stuffed up the hint and I should persevere with this idea.
> >
> > Any ideas about speeding up sorts?
> >
> > Regards,
> > James Crockart
> > Carsales.com.au
>
>
Received on Mon Jul 29 2002 - 08:34:10 CDT