The sort area size is the amount of memory in the pga which is dedicated to sorting. Each suer has his own sort area size.
If you set this to say 1Mb and 100 users were concurrently using their max sort area size, 100Mb of memmory will be consumed, bear this in mind. Tuning the sort area size can be done with the following code :

SET HEADING ON
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990

pause

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

The ratio of disk sorts to memory sorts should be less than 5%. If not increase sort_area_size.

When server process has used up all of it's allocted memory the data is split into smaller pieces and each piece is sorted individually in temporary tablespace. The sort pieces are then merged into the final result.

In temporary tablespace there is one sort segment (v$sort_segment). This segment extends when necessary and each extent can hold different users' sort pieces. This sort segment is dropped on shutdown. The first sort operation that needs temp tablespace creates the sort segment.

User is assigned a temp tablespace on creation.
Can also be altered with 'alter user'
Temp tablespace has contents as 'Temporary'
In 8i and onwards temp tablespace can be LMT. Create using keyword 'tempfile' for this.


SORT_AREA_SIZE : The amount of space reserved for sort operations in PGA.

SORT_AREA_RETAINED_SIZE : Shrink to this amount after sort completes. Usually set with MTS as all sorts are done in uga, this frees memmory in the UGA. There is no PGA in MTS.


Suresh