Re: Coalesce temp?

Jeff Schramm (jeff.schramm_at_REMOVETHIS.brite.com) wrote:
: I have a 24x7 database (version 7.3.2) that's been up for quite
: some time. I just checked dba_free_space_coalesced and noticed
: that my TEMP tablespace is at .5% coalesced. I'm thinking of
: setting its PCTFREE to 1 just so SMON will coalesce it, but I'm
: concerned that there may be penalties or side effects. Opinions?

I recommend against it.

If you change the PCTINCREASE (note -- I assume this is what you really meant) to 1, all temporary segments will be created with that value for PCTINCREASE. For segments with many extents, such as index builds for very large tables, you may run into problems with the extents getting too large.

More importantly, there is no reason to do this. There is no advantage to having the free space in TEMP coalesced. When Oracle needs to allocate an extent to a temporary segment, it will look for a free extent of the proper size; if it cannot find one, Oracle will allocate an extent from a larger chunk of free space. It is *more* work to allocate the extent if the free space is coalesced. If PCTINCREASE is 0, all extents in the TEMP tablespace will be the same size -- this means that not coalescing free space is a good thing, which is why SMON doesn't do it.

This assumes that you are only using the TEMP tablespace for temporary segments -- that is, the quota is 0 on TEMP for all users. If this is not the case, I strongly suggest that you make it so! For users' temporary tables, create a separate tablespace (we use "interim" for this purpose). This prevents problems of free space running out in TEMP unnecessarily when large temporary segments are needed.

-- 
-bn
random_at_interaccess.com	(PGP 2.6.2 public key available on request)
"There is no .signature -- only ZUUL!"