Re: Temporary tablespace size

From: Gary <rooty_hill2002_at_yahoo.com.au>
Date: 12 Aug 2003 19:20:40 -0700
Message-ID: <171bd226.0308121820.3d6944c9@posting.google.com>

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0308111526.45d7f73_at_posting.google.com>...
> rooty_hill2002_at_yahoo.com.au (Gary) wrote in message news:<171bd226.0308102036.ec5015_at_posting.google.com>...
> > Hi! All,
> >
> > I am Oracle newbie and I am using 9i2 on HP-UX 11i.
> >
> > I am puzzled by the size of the temporary tablespaces at the moment.
> > My Data Warehouse team is building a new data mart at the moment, lots
> > of sorting, I suppose. The database is of about 50G, it takes 3.5G
> > temporary tablespace and it just stays there and never changes, even
> > when after I shutdown the instance and no new session is connected.
> >
> > I thought temporary tablespace is the last thing I need to worry
> > about. I made it locally managed and big enough. Automated segment
> > space management is not available for temporary tablespace as far as I
> > know.
> >
> > Just curious to want to know. I know the fact that sort segment
> > created for each instance is reused and it is only dropped if the
> > tablespace is dropped. Does this have anything to do with it?
>
> It's supposed to be cleaned up on startup. I've seen where you need
> to startup twice to make it clean up. I don't know if this is a
> feature or a bug. Try it. Also see notes on metalink explaining temp
> ts's.
>
> >
> > Thanks in advance for all inputs.
> >
> > Gary
>
> jg

Joel,

Sorry it still there after shutdown the instance twice, I mean on Enterprise Manager (I am Oracle newbie and I started to be "Oracle DBA" 3 months ago), it still shows the 3253M out of 4000M of temporary space IS USED. Initially the word "USED" confused me. I am pretty sure it is actually a HWM as identified on space header after I did these search (I am the only use and no user seesion is connected when I did the query):

SQL> select total_blocks, used_blocks, free_blocks, max_blocks from v$sort_segment;

TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS MAX_BLOCKS ------------ ----------- ----------- ----------

      416384 0 416384 416384

SQL> select blocks_used, blocks_free from v$temp_space_header;

BLOCKS_USED BLOCKS_FREE
----------- -----------

     416512 95488

Some info:

1. DB_BLOCK_SIZE = 8k
2. HP_UX11i
3. 9i2
4. Extent size (Uniformed) 1024K. It is a shame I have too many

extents in this tablespace. Initially I used this size as we have relatively small temporary tablespace when 1024K was proper setting. Just because of the word "USED" Oracle uses on OEM allured me to increase 'TEMP' a couple of times and I forgot to increase the extent uniform size).

We can tell that totally 3253 extents HAVE EVER BEEN allocated for sorting purpose and NOW there is no part of it is used while on the space header of 'TEMP' these extents are MARKED as "space is currently being used" (exact words on p.3-180 of Oracle 9i Database Reference) and that is where OEM reads this statistical data from.

Brief conclusions:
1. The sort segment, as I mentioned in my first post, is reusable and the HWM will be always there until the temporary tablespace is dropped. This mark will be shown as "USED" space mark lives long as the tablespace itself. Luckily Oracle doesn't treat this HWM as the ones in permanent tablespaces.

2. Words like "USED"/"SPACE IS CURRENTLY BEING USED" in Oracle doc, for this specific case, are very confusing, as the matter of fact, wrong. The inconsistence between them and the fact certainly confused me.

3. I will not panic next time if the "USED" mark approaches 95% because I will certainly make sure it is "REALLY USED" first before I give them more space and change the extent uniform size.

Please let me know I got it wrong.

Thanks for all gents' inputs in this thread.

Regards,

Gary Received on Tue Aug 12 2003 - 21:20:40 CDT