Re: Are temp segments being freed automacically or not ?

There is only ONE TEMP segment in Oracle 9i, there are many extents which belong to the TEMP segment.

You may want to run these:

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS

TEMP                 511744      127488      384256

As you can see, there is only one segment. There are a total of 511744 blocks, of these only 384256 are free.

So what's using the segments:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

TABLESPACE	SEGFILE#	SEGBLK#	BLOCKS	SID	SERIAL#	STATUS
TEMP	201	60041	127232	45	43983	ACTIVE
TEMP	202	17417	128	43	16147	INACTIVE
TEMP	202	115849	128	17	9834	INACTIVE

This show's the processes consumming the temp extents, add (a.username, a.osuser) for additional information, The sum of the BLOCKS is 127488... matching the query above (USED_BLOCKS).

When we run:

SELECT s.saddr, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks  FROM v$session s, v$sort_usage u, v$sqlarea a  WHERE s.saddr=u.session_addr
 AND s.sql_address = a.address;

SADDR	TABLESPACE	CONTENTS	EXTENTS	BLOCKS
C00000009C379720	TEMP	TEMPORARY	1	128
C00000009C38BEE0	TEMP	TEMPORARY	994	127232
C00000009C38A9C0	TEMP	TEMPORARY	1	128

We can see that there are 996 Extents allocated. The extents size is that matching an extents from the tablespace NEXT extent allocation

 select tablespace_name, next_extent/block_size "Extent size" from dba_tablespaces where tablespace_name = 'TEMP'

TABLESPACE_NAME	Extent size
TEMP	128

Therefore, each extent allocated is 128 BLOCKS, or 1MB in this case. EXTENTS are not shared by processes. As you can see there is a large sort occuring above, i.e. 994 EXTENTS being consumed.

So I suppose it's not about deallocation of "Segments", but freeing up of "EXTENTS", usually if you are running out of TEMP tablespace, a query is incorrect, or you genuinely don't have enough TEMP space allocated. I had a situation this week in which a developer told me there wasn't enought TEMP space and his process keep getting can't extend TEMP tablpspace by 128. Even though the space was 4GB. I found out the query was missing a join condition and was causing a massive product. My TEMP tablespace still indicates 99% FULL, although as you can see the above queries, there is still much space available.

Hope this helps,

Cheers,

Greg Johnson

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0307241208.4320b858_at_posting.google.com>...
> 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 Fri Jul 25 2003 - 16:08:56 CDT