The sort run is one thing. The merge phase is another. A big sort will
generate a number of short sort runs. Then we have to merge those partial
sorts into a single completely sorted set of rows for returning (or
'fetching') back to you. The sort runs are done in sort_area_size, the
merge phase in sort_area_retained_size. Therefore, if the _retained_size is
too small, the sort runs might not be able to be merged in memory, without
being swapped to disk.
Regards
HJR
--
Resources for Oracle: http://www.hjrdba.com
===============================
"dias" <ydias_at_hotmail.com> wrote in message
news: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".
>
> 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 - 12:38:59 CST
