Re: After Sort what?

Thanks Jonathan. You Rock.
But just need a little bit of clarification. (I hope you don't get pissed of on me)

1. So sort_area_retained_size is a memory area which is retained, it has nothing to do with the Sorted rows (sorted rows are in the temp tablespace) Am i correct ?
2. Now who reads the data from the temp tablespace ? is it server process ?
3. If i don't have a temp tablespace in my database, then where does it store the sorted result set?

Thanks & God Bless
Pradeep

Jonathan Lewis wrote:
> "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
Received on Wed Nov 30 2005 - 03:06:11 CST