Integer 

Dynamic. Scope= ALTER SESSION, ALTER SYSTEM ... DEFERRED. 

Derived from SORT_AREA_SIZE 

from the value equivalent of two database blocks to the value of SORT_AREA_SIZE 

SORT_AREA_RETAINED_SIZE specifies in bytes the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.  

Oracle may allocate multiple sort spaces of this size for each query. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, however, additional concurrent sorts are required, and each sort keeps its own memory area. If the multi-threaded server is used, allocation is to the SGA until the value in SORT_AREA_RETAINED_SIZE is reached. The difference between SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE is allocated to the PGA. 

Note: The default value as reflected in the V$PARAMETER dynamic performance view is 0. However, if you do not explicitly set this parameter, Oracle actually uses the value of the SORT_AREA_SIZE parameter. 

See Also:

• Oracle8i Designing and Tuning for Performance for more information on setting this parameter.

• "SORT_AREA_SIZE".