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

Which version of Oracle
How many rows, roughly, in the input
How many rows, roughly, in the output
How many columns do you sum
Do you sum column in multiple ways (e.g. sum (a), sum(b), sum(a)+sum(b)) Do you use any count(distinct), (e.g. sum(sales)/count(distinct salesmen) Are you using parallel query
What are the initial/next/pctincrease values of the Temp tablespace

The 'sort area size' parameter in the init.ora file stops a session from grabbing all available memory (well, apart from a few bugs) when sorting. If you want to take advantage of your 400MB of free memory then you may want to adjust this parameter.

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

Robert Chung wrote in message <36d60012.9980703_at_news.mindspring.com>...
>
>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 - 02:48:18 CST