-----Original Message-----
From: Moritz, Jean (JFMO) [mailto:JFMO@chevron.com]
Sent: Friday, July 24, 1998 1:23 PM
To: 'akaplan@interaccess.com'
Subject: Temporary Segments
>We have a 2Gb temp tablespace. One of our reports created a temporary
>segment with 1969 extents (initial 1M, next 1M) and filled the
>tablespace. After 24 hours, this segment had not disappeared. To clear
>out the tablespace, I bounced the instance early this morning. I
>thought Oracle would reuse temporary segments. We think our problem is
>with the 'ORDER BY' clauses in the application and changing the instance
>OPTIMIZER_MODE to FIRST_ROWS. The optimizer setting improved the time
>on the reports, but has caused huge temporary segments.
>Some info from you was forwarded from another DBA.
>
>Do temporary segments get freed by smon/pmon? If not, is there a way to
>manually clear out these segments other than dropping the tablespace or
>bouncing the instance?
>
>Thanks so much. I just checked your WEB page, but could not get to the
>server.
>
>Jean F. Moritz
>Chevron Information Technology Co.
>CTN 596-3276
>
Jean,
Yes, the TEMP segments (probably owned by SYS) do get cleared out, although it
sometimes takes a while. You just have to wait it out.
One reason for swapping is the SORT_AREA_SIZE is too small. Once Oracle uses
that space in memory, it has to swap out to disk.
To improve performance, it sounds like you should change your default storage
for your TEMP tablespace:
ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 100M NEXT 100M);
That way, there would have been only 20 extents instead of 1969.
Best of luck,
-Ari
Back to Ari Kaplan's Home Page