Sort segment

could some one tell the concept Oracle uses in sorting ...using temporary segments, iit is said that the same temporary segment will be used by all users for sorting ,, will each user get a different extent for his sort and is the temporary segment allocated tablespace wise or it is one for an instance ??

could also the SORT_AREA_SIZE and SORT_AREA_RETAINED SIZE be clarified ???

regards,
sharmila

Sort is an operation performed by oracle when u want to arrange data with respect to by name,or company.
Generally when u use group by,order by or any group function in ur sql statement oracle will perform sort operation on the column used in the group by or order by clause.

SORT_AREA_SIZE is the parameter set to allocate this amount of memory for oracle to perform sort in the memory.
If the sort operation requires more space than this,the oracle will allocate temporary segment in the temporary tables and perform sort operation their.

SORT_AREA_RETAINED_SIZE is the amount of sort area retained after oracle performs sort operation.

temporary tablespace is used by all the users in the database.

regards
anandkl

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

Thanks Suresh good "sort " of an explanation