Re: After Sort what?

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

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

"Pradeep" <agarwalp_at_eeism.com> wrote in message 
news:1133282710.253507.177490_at_f14g2000cwb.googlegroups.com...


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

>

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

> sorted rows or what?

>

> Please clarify?

>