Re: Sorts and IO in the temporary tablespace

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

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1007655218.26003.0.nnrp-08.9e984b29_at_news.demon.co.uk>...
> There may be very little connection between
> the two.
>
> The temporary tablespace is the target for
> large hash joins, temporary tables, and temporary
> LOBs in 8.1
>
> Also, a genuine sort that does not 'use the disk'
> for sorting may also dump the result set to disk
> anyway at the end of the sort if the sort_area_retained_size
> is smaller than the sort_area_size.
>
>
>
>
> --
> 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.0112060721.3fc814c3_at_posting.google.com>...
> >Hi,
> >
> >I have a big diffrence between the sorts in the SYSSTAT section of a
> >report.txt and IO on the temporary tablespace.
> >
> >- Sorts (disk) = 16
> >- Temporary tablespace:
> > - READS = 7794
> > - BLKS_READ = 95301
> > - WRITES = 11999
> > - BLK_WRT = 11999
> >
> >My question is what is the relation between the number of sorts and
> >the IO in the temporary tablespace. Are all IO in the temporary
> >tablespace caused by sorts or not ?
> >
> >Thanks
Received on Fri Dec 07 2001 - 03:38:35 CST