Re: After Sort what?

Pradeep 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

What I don't understand is the above sentence.

>
> "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 ?
>

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.

> Q2. what does SORT_AREA_RETAINED_SIZE? does it stores some of the
> sorted rows or what?
>

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.

> Please clarify?

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

David Fitzjarrell Received on Tue Nov 29 2005 - 11:59:27 CST