Oracle uses the INIT.ORA parameter
SORT_AREA_SIZE to allocate memory for
use in sorting data. When a sort cannot be completed in memory,
Oracle uses temporary segments in the database, which is considerably
slower. A careful balance is required for SORT_AREA_SIZE, since large
sizes can dramatically increase performance by decreasing I/O, but
will also use up memory and can result in paging.
Tip
Remember that this parameter applies to each user process. Each user
process performing a sort will have SORT_AREA_SIZE memory allocated.
So, if SORT_AREA_SIZE is set to 1 megabyte, and 100 user processes
are performing sorts, a total of 100 megabytes of memory may be
allocated.