Re: Are temp segments being freed automacically or not ? - ThanX for *these* selects !

Thanks for these SQL-statements, I checked them out on quiet and heavy times, they work great !
And I did not know their joins well enough. This is precisely the info I needed 8-D.

I love this group !

Jan

"grjohnson" <Johnsog123_at_hotmail.com> schrieb im Newsbeitrag news:32b8a689.0307251308.7aabb42b_at_posting.google.com...
> 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 Mon Jul 28 2003 - 02:07:29 CDT