Re: Sorting

If you use Oracle7.0, run the query below immediately BEFORE and AFTER running your SELECT...ORDER BY query (make sure there are no other queries running at the same time):

SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts(memory)','sorts(disks)');

If the above query shows a large increase for the value of 'sorts(disks)', then increase the value of the parameter SORT_AREA_SIZE in the INIT.ORA file.

If you are using Oracle 7.1 and above and have a multi-processor server, take advantage of the Parallel Query Option (PQO) by giving your table a certain degree of parallelism (ALTER TABLE.....PARALLEL (DEGREE n)).

If you are using Oracle7.2 and above, take advantage of the "sort direct writes" feature. Set the following INIT.ORA parameters:

SORT_DIRECT_WRITES=TRUE
SORT_WRITE_BUFFERS=4 (any value between 2 and 8)
SORT_WRITE_BUFFER_SIZE=65536

The above values are recommended by ORACLE Corp. for best performance. According to ORACLE Corp., this feature is your best bet for improving ("dramatically") the performance of sort operations.

If you are using Oracle7.3, in addition to the sort direct writes feature, you could use the "dedicated temporary tablespace" feature. Change your TEMP_DATA tablespace to make it temporary, i.e. so it will store only temporary objects:

ALTER TABLESPACE temp_data TEMPORARY;

In 7.3, you can also look into the V$SORT_SEGMENTS data dictionary table. It contains statistics re the usage of the sort segments in your database. It has one row for each dedicated temporary tablespace.

Let us know how it turned out.

I hope this helps.

Michael Serbanescu
Sr. Consultant, MIACO Corporation