Re: temporary segments

From: David F. Newman <buzzwang_at_churchill.ourvillage.com>
Date: 01 Oct 1999 12:46:39 -0400
Message-ID: <86vh8rf2q8.fsf@churchill.ourvillage.com>

<terrysutton_at_usa.net> writes:

> Kenneth C Stahl wrote in message <37F0E095.150B8686_at_Unforgettable.com>...

> >Not necessarily. I've seen this type of thing many times. There really
> >doesn't seem to be any rhyme or reason as to why segments are built at a
> >particular location.
> >
> >I suspect (but have not been able to prove) that if you were to do a ALTER
> >TABLESPACE COALESCE it would probably reset things so that Oracle would
> >start at the first empty block, but there doesn't seem to be any guarentees
> >of that either. The thing that appears to be happening here is the engine
> >itself is building temporary segments for its own purposes and that may be
> >perfectly valid under any number of circumstances so the question becomes
> >whether you hide them away in the SYSTEM tablespace and forget they even
> >exist or tolerate them being in the designated tablespace for temporary
> >segments chiefly used by user applications.

I have finally figured this out. When Oracle grabs a new extent it is created at the begining of a free extent, but not the first free extent. I seems to choose the free extent at random. I noticed this when I had to rebuild an index. With a coalesced tablespace Oracle started grabing space at the begining of the tablespace until it was filled. For the next index I had to rebuild the temporary tablespace was fragmented with free extents. Oracle started grabing free extents at random until the tablespace was filled. It was kind of neat to watch it with tablespace manager.

So armed with this new information I set the pctincrease of the tablespace to 1 so that when the DB was shutdown at night the tablespace was automatically coalesced. When the DB started up again my temporary segments were at the beginning of the tablespace and not at the end.

p.s. I'm usually a command-line sql*plus kind of guy, but this is one situation where a GUI came in handy.

--
David F. Newman
Oracle DBA
dnewman_at_ourvillage.com Received on Fri Oct 01 1999 - 11:46:39 CDT