Re: Fragmentation Problems

In article <3615_at_unixhub.SLAC.Stanford.EDU> ian_at_unixhub.SLAC.Stanford.EDU (Ian A. MacGregor) writes:
>In article <1992Apr2.072539.14769_at_scammell.ecos.tne.oz.au>,
>brt_at_scammell.ecos.tne.oz.au (Brett Dunstan) writes:
>|>
>|> I have a small fragmentation problem with the ORACLE database
>|>that is currently in use on a sparc 2. The result of the following

[free space stuff deleted]

>|> It seems odd that although many of the blocks are physically
>|>next to each other Oracle cannot join them together. This may seem
>|>a very easy question, but, is there a way of fooling Oracle to join
>|>them together, either by creating and dropping tables, or any other
>|>method.
 

>The results of selecting from user_free_space are somewhat misleading. Oracle
>can join the blocks together, but does not until it needs to do so. If you
>create an object with large enough extents or if the next extent for a current
>object is of sufficient size, the joining of contiguous free space extents will
>occur. I believe Oracle adopted this strategy for performance reasons.

Kind of. I doesn't really "join the blocks together." in the true sense of the word. Yes, they can be combined to create a single object but when you drop the object, the extents that were used by that object are released back to free block list in the same or smaller chunks than before.

Net gain: Nada.

Your best defrag option is still to export the data, drop the tablespace and reimport. Currently, there is no other option. I guess you could just keep adding files, but that can get expensive, plus your performance will eventually begin to take a hit due to continual increase in the number of the individual extents that the database must keep track of. Ain't it fun?

>|> Currently we drop the tablespace ( including its contents ),
>|>re-create it and then re-insert the indexes or reimport the data in order
>|>to remove any fragmentation ( ie: create one large extent ).

Indeed, the best solution.

Dale Cooper, DBA
Center for Seismic Studies
Arlington, VA Received on Mon Apr 06 1992 - 16:22:43 CEST