What I don't understand is the above sentence.

You 'don't get this' because you've taken the quote out of context by
removing the text 'except the amount specified by the
SORT_AREA_RETAINED_SIZE parameter.'  Not ALL of the sort area is
released; the amount configured in SORT_AREA_RETAINED_SIZE is,
amazingly, retained until the last row is returned.  By default
SORT_AREA_SIZE is set to 65536; SORT_AREA_RETAINED_SIZE is equal to at
least 2 database blocks and can be set to as high as the entire value
for SORT_AREA_SIZE.

You clearly need to read the Concepts Guide and dig deep into the
manuals at tahiti.oracle.com.  SORT_AREA_SIZE is a maximum value for
the allocated sort area for a user session.  The sort area is allocated
in units of database blocks, allocated as needed, until reaching
SORT_AREA_SIZE at which time no further allocations are performed.
SORT_AREA_RETAINED_SIZE is just that, the portion of the sort area
retained after a sort is completed but before the rows are returned to
the calling session.  Note it is a PORTION of the already allocated
SORT AREA.  Grasping that concept should allow the quote from the
manual to make sense.

I have, and you REALLY need to read the fine manuals if you want more
information on this topic.

David Fitzjarrell

I agree that SORT_AREA_RETAINED_SIZE is particularly poorly documented.

In the case of a disk sort, SORT_AREA_SIZE is the max size of the memory
scratch space for doing the partial sorts which are written to temp disk.
Once the sort is done and only the final merge is left, you no longer get
much benefit from having a lot of this scratch space.  My understanding (or
guess, if you prefer) is that it drops down to SORT_AREA_RETAINED_SIZE for
doing this final merge and buffering.

I don't think Oracle uses an in-place memory sort, so even for a sort which
is done in memory there is going to be no-longer used space once the sort
it done, and that can be freed.  I don't know why Oracle would need a
parameter to tell it how much of this space to free, it seems like it would
just free all of it that isn't needed to hold the results.  From something
I've seen under 8i, which I no longer recall exactly what it was, I had the
suspicion that, even for in-memory sorts, the part of the result that
exceeds SORT_AREA_RETAINED_SIZE is written to disk for safe keeping so that
the rest of SORT_AREA_SIZE can be freed.

Xho

Assuming workarea_size_policy = manual.

The default setting for sort_area_retained_size
is zero, which means it matches sort_area_size
whatever you do to the sort_area_size.

Assume,. therefore, you have set the
sort_area_retained_size to a non-zero value.

When your session starts a sort, it will acquire
memory a few KB at a time in the UGA, up
to the limit of sort_area_retained_size. If your
sort has not completed in memory at that point,
your session will start to demand memory from
the PGA - up to sort_area_size - sort_area_retained_size.
(so the total demand can reach sort_area_size, but
is split into two parts).

If your sort completes in memory, Oracle dumps the
data to disc, releases the PGA memory allocation,
and retains the UGA memory allocation (although
in recent versions it seems to limit the retention to
1MB) in order to read back and return the rows
to the client process as FETCH calls are made.

If the sort_area_retained_size is left at zero, then
sort_area_retained_size  = sort_area_size, which
means that there is no excess to demand in the PGA,
and when the sort completes, it does not dump to
disc.

Things get a little more complicated if the
sort_area_size is too small to hold the entire
data set, but the principle is the same - the
sort_area_retained_size acts as a buffer
between the sorted data on disc, and the
next operation that needs to see that data