Temporary table space
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?
Ans1:
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.
Ans2:
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.
Ans3:
More important is to check the offensive sql query codes which may
not use
proper index, instead they use all merge and join!