Re: Sort operations

You have to remember that you can
allocate multi sort_area_retained_size, but only a single sort_area_size.

If you look through v$sql at the SORTS
columns, you may find that some statements require multiple sort cursors open concurrently. In these cases the sort_area_size allocation is used to do the result is dumped to disc and a sort_area_retained_size allocation is used to feed the results from disc to the next step of the execution path.

The worst case I've seen recently was
SORTS=7 in v$sql, but you have to
get quite exotic SQL these days to
achieve that.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Simon Waters wrote in message
<950279225.1472.0.nnrp-13.c1ed6dcb_at_news.demon.co.uk>...
>I noticed also on 7.3.4 that the SORT_AREA_RETAINED_SIZE defaults to the
>SORT_AREA_SIZE, if you set the SORT_AREA_SIZE in the init file.
>
>Seems a sensible default, but I could not find it explained in the manual,
>this area does seem a tad underdocumented.
>
>Presumably you set the retained size smaller than the sort size if you need
>to allocate more memory than you can afford to lose permanently (in
>SORT_AREA_SIZE)?
>
> Simon, all of whose 'sorts' happen in memory.
>
>Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message
>news:38A41691.39C18A22_at_edcmail.cr.usgs.gov...
>Alexander,
>
>This is wrong. The SORT_AREA_SIZE controls the maximum amount of memory
>allocated to an in-memory sort. If the sort exceeds this size, then the
>sort is broken into smaller pieces and sorted (at least partially) on
>disk. I'd trust the Oracle documentation before Special Edition.
>
>
>
Received on Fri Feb 11 2000 - 09:50:50 CST