Re: After Sort what?

"Pradeep" <agarwalp_at_eeism.com> wrote:
> The docs states that
> " Sort_area_size is the memory used to for sort." This is understood
>
> What i don't understand 1005 is the below
>
> "After the sort is complete, but before the rows are returned, Oracle
> releases all of the memory allocated for the sort, except the amount
> specified by the
> SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
> Oracle
> releases the remainder of the memory."
>
> Q1. <<After the sort is complete, but before the rows are returned,
> Oracle
> releases all of the memory allocated for the sort>> I really don't get
> this. What happens to the sorted rows? How does it provides the sorted
> rows ?

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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB