yes it does -- you have many retained perhaps for a query and one sort area size per process (think PQ for a moment -- separate processes/threads).


Say you have a 1 meg retained size and a 2meg sort area size. Oracle will begin by allocated the retained size in the UGA (usually in the pga, but in shared server this is in the SGA). when that hits 1meg, it'll start allocating the sort area size dynamically in the PGA (always the PGA). the retained size will stick around as long as we need it (typically till the result set is exhausted/cursor closed). the sort area size will be released for reuse after the call that required it is done.

that is not correct.

it will allocate up to sort_area_retained_size in the UGA and the spill over into the "pure" PGA up to sort_area_size if the sort exceeds the sort_area_retained_size AND sort_area_size is greater then sort_area_retained_size.

the sort_area_retained_size parameter is often misunderstood. At a high level, this is what happens:

to do a sort, Oracle allocates chunks of memory up to a maximum of sort_area_size.

As each chunk is allocated -- Oracle checks to see if the sort area is less then the sort_area_retained_size.

If so, this chunk is allocated in memory that survives from call to call (in the UGA -- where ever the UGA might be -- in the pga or sga). This memory survives as long as needed (across many calls potentially to fetch rows and such)

If not, this chunk is allocated in the "pure pga" and freed at the end of the call. This memory does not survive across calls at all.

Sort area memory is not necessarily contigous memory -- hence bits of it could be in the SGA, some in the PGA -- it matters not to Oracle.

see also
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1197.htm#1024241 <code>

...
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 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.
........

sorts are done in the UGA upto sort_area_retained_size and then spill into the PGA.

In shared server, the UGA is in the SGA.
In dedicated server, the UGA is in the PGA.

that is the difference.

except for shared server connections in 9i, yes.

it is not that it is "formally obsolete"

it is that it is not used when workarea_size_policy=auto.

the docs for that init.ora parameter state that when set to manual, the *_size parameters are used. (else, not)

the docs for sort_area_size state that:

...
Note: Oracle does not recommend using the SORT_AREA_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_SIZE is retained for backward compatibility.
........

so, just check out the reference guide and read about the two parameters.

depends on the release. 8i and before, the pga memory is freed back to the process (for other allocations by that process) but not to the OS.

With workareas, they can be dynamically attached and freed back to the OS.

You can see this
</code> http://oracle.apress.com//betabooks/betabook.html?betabook_id=30&APRESSESSID=c5e74fa001f64357db462f5e677af1b1 <code>
at work.

....
You can see from the Autotrace statistics, that Oracle (using autotrace in SQL*Plus) has reported that we've done 8 recursive calls of some sort during the processing of our query. 3 sorts in memory and 1 sort that spilled to disk. By the time we finished our query and exhausted the result set, we can see our UGA memory went back down to what it started at (we released the sort areas from our UGA) and the PGA shrunk back somewhat (note that in Oracle 8i and before, you would not expect to see the PGA shrink back at all, this is new with Oracle 9i and later). Now, let's retry that operation but play around with the size of our SORT_AREA increasing it to 1 MB:
.......