Re: SYS is sorting to disk

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/03/06
Message-ID: <952320638.23092.0.nnrp-13.9e984b29@news.demon.co.uk>#1/1

This is expected behaviour.
a) All temporary segments used in sorting belong to SYS

b) The sort activity takes place in the sort_area_size, but

if the sort_area_retained_size is smaller than the result set from the sort, then the result set is dumped to the TEMP segment and a memory area of sort_area_retained_size is used from the UGA to return the result set to the user (or as Mark Powell points out, to the next phase in the execution plan).

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

NetComrade wrote in message <38c08c0c.6834606_at_news.earthlink.net>...


>I set up a new db with a fairly large TEMP tablespace, later on I

>started getting pages that I am running out of TEMP space. So I

>started looking for the problem.

>

>I found that SYS schema is sorting to disk, and even when I decreased

>INITIAL and NEXT default TEMP extents to 1/16th of SORT_AREA_SIZE, SYS

>is still writing to disk, and in exactly 1M segments (my latest

>defualt Initial and NEXT)

>

>So it looks like SYS is avoiding sorting in memory, does anybody know

>why?

>

>Thanx.

Received on Mon Mar 06 2000 - 00:00:00 CST