Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 100M table created a giga byte temp tablespace!
More important is to check the offensive sql query codes which may not use
proper index, instead they use all merge and join!
Christopher M. Day <christopher.day_at_rdbms.freeserve.co.uk> wrote in article
<36D671BA.FF8A4B50_at_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 Sat Feb 27 1999 - 16:29:21 CST