Re: After Sort what?

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

Jonathan Lewis wrote:
> 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?
> >
Received on Tue Nov 29 2005 - 22:35:57 CST