Re: Coalesce Free Space

In article <FOH65.13$7R.1829_at_newreader.ukcore.bt.net>,   "Kevin A Lewis" <Kevin_A_Lewis_at_Hotmail.com> wrote:
> There are no downsides that I am aware of.
>
> Only upsides
>
> Regards
>
> --
> Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich
 United
> Kingdom)
> <Kevin_A_Lewis_at_Hotmail.com>
>
> The views expressed herein by the author of this document
> are not necessarily those of BOCM PAULS Ltd.
> <thiko_at_my-deja.com> wrote in message news:8jf0p0
 $r7c$1_at_nnrp1.deja.com...
> > Hi
> >
> > I have queried this on our database:
> >
> > SELECT tablespace_name, total_blocks,
> > blocks_coalesced, percent_blocks_coalesced
> > FROM dba_free_space_coalesced
> > WHERE percent_blocks_coalesced<>100;
> >
> > and have found that yes we do have adjacent free extents in the
> > database. I now would like to issue this command to coalesce them:
> >
> > ALTER TABLESPACE <tablespace_name> COALESCE;
> >
> > I would like to know what the down side (if any) of doing this is.
> > Especially if it is quite a large database. e.g will anything become
> > unavaliable, will things slow down? Is it a good idea?
> >
> > Thanks
> >
> > Thiko!
> >

Excessive coalescing can sometimes result in an ORA-01575 'timeout waiting for space management resources' error. Every operation that attemps to allocate or dealloacte an extent has to get the ST lock (there is only one). If the temporary segments are being written to a permanent tablespace then it is used to allocate temp segments so if heavy sorting to disk and coalescing of a lot of small extents at the same time you might see this. It is however, unlikely unless maybe if you are running OPS. With parallel server it is not a good thing to try to have both smon's attempt to coalesce the same extents at the same time. Oracle support used to advise to turn coalesing off on one instance to elviate this error.

The use to a temporary mode tablespace for temp segments and/or locally managed tablespace of 8.1 greatly reduce the use of the ST lock.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.