Re: 100M table created a giga byte temp tablespace!

From: Christopher M. Day <christopher.day_at_rdbms.freeserve.co.uk>
Date: Fri, 26 Feb 1999 10:04:42 +0000
Message-ID: <36D671BA.FF8A4B50@rdbms.freeserve.co.uk>

Robert,

Check the sort_area_size parameter, you might want to increase it to do more sorting in memory, before the sort pages are written to temp. Another tip would be to size the extents in your temporary tablespace to be multiples of the sort_area_size.

Chris

Robert Chung wrote:
>
> I have a table that takes up a little more than 100 MBytes. One of
> the user executed a SQL statement that processes every single row in
> the table and produces some report. (It sums numbers grouping by
> concatenated primary key with four columns.) Nothing fancy. Nothing
> more. But this SQL statement causes temporary tablespace to grow over
> a giga bytes. While Oracle is processing SQL statement, I observed
> operating system, and it said it had over 400Mbyes of free memory.
> Obviously, Oracle is hitting hard disks big time for temporary
> tablespace, but not using all those free memory. Is there something
> that I should do to fix this? Thanks in advance.
Received on Fri Feb 26 1999 - 04:04:42 CST