Initialization Parameters, 173 of 188

SORT_AREA_SIZE

Parameter type:
Integer

Parameter class:
Dynamic. Scope= ALTER SESSION, ALTER SYSTEM ... DEFERRED.

Default value:
Operating system dependent

Range of values:
Minimum: the value equivalent of six database blocks
Maximum: operating system dependent

SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases memory down to the size specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never occur. Only one memory area of SORT_AREA_SIZE exists for each user process at any time.
Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

The default is adequate for most OLTP operations. You might want to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.

See Also:

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

Oracle8i Concepts for information on logical storage structures such as sort areas.

Your operating system specific Oracle documentation for the default value on your system.

"SORT_AREA_RETAINED_SIZE".

Parameter type:	Integer
Parameter class:	Dynamic. Scope= `ALTER` `SESSION`, `ALTER` `SYSTEM` ... `DEFERRED.`
Default value:	Operating system dependent
Range of values:	Minimum: the value equivalent of six database blocks Maximum: operating system dependent
`SORT_AREA_SIZE` specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases memory down to the size specified by the `SORT_AREA_RETAINED_SIZE` parameter. After the last row is returned, Oracle releases the remainder of the memory.
Increasing `SORT_AREA_SIZE` size improves the efficiency of large sorts. Multiple allocations never occur. Only one memory area of `SORT_AREA_SIZE` exists for each user process at any time. Larger values of `SORT_AREA_SIZE` permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.
The default is adequate for most OLTP operations. You might want to adjust this parameter for decision support systems, batch jobs, or large `CREATE` `INDEX` operations.
See Also: Oracle8i Designing and Tuning for Performance for more information on setting this parameter. Oracle8i Concepts for information on logical storage structures such as sort areas. Your operating system specific Oracle documentation for the default value on your system. "SORT_AREA_RETAINED_SIZE".