Re: After Sort what?

"Pradeep" <agarwalp_at_eeism.com> wrote in message news:1133325357.811093.96460_at_z14g2000cwz.googlegroups.com...
> Thanks Jonathan, it opened some of the shut windows
> But few questions though. Just assume for explanation sake, If my
> sort_area_retained_size can store 1000 sorted rows (or do sorting of
> 1000 rows. I assume both are same). If i do a sort of 2000 rows it will
> (as you said) do the sorting with the help of
> sort_area_size-sort_area_retained_size. So now the 2000 rows are
> sorted. Now oracle releases the sort_area_size-sort_area_retained_size
> amount of memory but retains sort_area_retained_size. So UGA might have
> 1000 sorted rows in it. Now what happens to the rest of the 1000 rows.
> Is this what goes to the temp tbs ?
>
> Thanks a lot for your time & energy.
> Pradeep
>

Note particularly this bit: "Oracle dumps the data to disc". This means Oracle dumps the ENTIRE sorted data set, in the correct order, into your temp tablespace. Then it starts reading it back in order to return it to the user (or next execution path operation). When the dump is complete, the memory in the PGA (the excess of sort_area_size over sort_area_retained_size) is freed.

In passing:
I suspect that the sort_area_retained_size got its name because, as part of the UGA it could not be released, not because it meant anything particularly special for sorting. (i.e. it's the "retained" bit that is significant, not the "sort" bit).

>>
>> Assume,. therefore, you have set the
>> sort_area_retained_size to a non-zero value.
>>
>> 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.
>>

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