Re: Sorts and IO in the temporary tablespace

Assume you sort 10,000 rows of
100 bytes each for a total of 1M,
and then fetch them 10 rows at
a time.

If your sort_area_size is 2M but the
retained size is 64K, the entire sort
will take place in memory, but you can
only keep 64K of sorted data in memory
whilst returning the rows - so the rest
has to written to the temporary segment
until it is fetched.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

dias wrote in message <55a68b47.0112070138.2bfafd4f_at_posting.google.com>...


>Hi,

>Thank you all for the responses.

>

>Just one thing I don't understand. Why a sort have to write to disk if

>the sort_area_retained_size is smaller than the sort_area_size.

>

>The Oracle doucmentation says that the sort_area_retained_size is "the

>maximum amount of the user global area (UGA) memory retained after a

>sort run completes".