1.330 SORT_AREA_RETAINED_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.
Property | Description |
---|---|
Parameter type |
Integer |
Default value |
Derived from |
Modifiable |
|
Modifiable in a PDB |
Yes |
Range of values |
From the value equivalent of two database blocks to the value of |
Basic |
No |
Note:
Oracle does not recommend using the SORT_AREA_RETAINED_SIZE
parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET
instead. SORT_AREA_RETAINED_SIZE
is retained for backward compatibility.
Oracle may allocate multiple sort spaces of this size for each query. Usually, only one or two sorts occur simultaneously, even for complex queries. In some cases, however, additional concurrent sorts are required, and each sort keeps its own memory area. If the shared 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:
-
Oracle Database Performance Tuning Guide for information on setting the values of this parameter and the
SORT_AREA_SIZE
parameter to tune sort operations using shared servers