Re: Are temp segments being freed automacically or not ?

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<3F1FECF7.4770DDAA_at_remove_spam.peasland.com>...
> TEMP segments will be freed, eventually and automatically.
>
> Sometimes, a user requests a sort and needs TEMP space to facilitate
> that sort. So a TEMP segment is allocated by the system. The user
> finishes with the sort and no longer needs the TEMP space. The system
> could automatically free this temp segment at that time. But what if
> another user needs a temp segment? Why not keep around the first temp
> segment for a while to see if another user will need it? That way, the
> system doesn't have to allocate something is just deallocated. That's
> the typical behaviour of TEMP segments in Oracle.
>
> HTH,
> Brian
>
> Jan Gelbrich wrote:
> >
> > Hello,
> >
> > I would like to know if my observation is something to worry about or not
> > ...
> >
> > My system:
> > Oracle EE 8.1.7 on AIX4.3.3, 25 GB of data, 200 sessions,
> > dedicated server config, closed inhouse app based on Forms.
> >
> > Some months ago we rebounced the instance with a TEMPORARY tablespace
> > (just as things should be - and should have been in the first place ...),
> > and we saw about 54% usage - OK. Over weeks and weeks, there was no
> > recognizable change
> > to this figure, so we thought, well it seemed to have been right
> > dimensioned.
> >
> > Since yesterday we saw 80% and began to wonder ...
> >
> > Ok, tablespace increasing is not the issue here,
> > I just rememebered that in some manuals, also in Guy Harrisons tuning book,
> > it says that temporary segments (hold by SYS) are allocated automatically
> > by the instance, *and* deallocated the same way.
> >
> > But what I see seems to be a HWM of the segment that never decreases.
> > One thing is to add: the TS is still a DMT ... but would a LMT behave so
> > much different ?
> >
> > So, in the end: how do temporary tablespaces behave ?
> >
> > Any tips would be greatly appreciated
> > (besides RTFM, I do that everyday)
> >
> > ThanX in advance,
> > Jan
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"

Actually, I believe that if the temp tablespace is created as "create temporary tablespace" or "create tablespace temp temporary" the temp SEGMENT once allocated to the instance will not be freed until the instance is shut down. EXTENTS within the temp segment will however be made available for reuse by other sessions as the using session sort steps end. You can view the temp segment created to support either of the above two commands in v$sort_segment and the using sessions in v$sort_usage. These views are not populated for temp tablespaces created as "create tablespace temp [permanent]" in which case multiple segments are allocated and freed.   

HTH -- Mark D Powell -- Received on Thu Jul 24 2003 - 15:08:05 CDT