The Oracle (tm) Users' Co-Operative FAQ

What is tablespace fragmentation and should I worry about it ?

Author's name: Mark D Powell

Author's Email:

Date written: 4th Sept 2001

Oracle version(s):

What is tablespace fragmentation and should I worry about it ?

Back to index of questions

In an ideal world every Oracle object would be sized such that it fit into one and only one extent and would never grow beyond that initial extent. But the world is not ideal and the eventual size for many objects is often not known. As such objects are expected and allowed to take extents. If the tablespace is built without specifying default storage parameters and the objects are built using the Oracle default tablespace parameters as many canned (purchased) software packages are then the objects start with only 5 Oracle data blocks and ask for each new extent after the first additional extent to be 50% larger than the prior allocation. If some of these objects are work tables/indexes that get truncated, the objects are later dropped, or the objects are relocated to different tablespaces for some reason, then the tablespace ends up with multiple free extents of varying sizes existing scattered throughout the tablespace. Many of these free extents can be sizes that no object in the tablespace has as its calculated next extent size so that they are unusable for all practical purposes. Eventually a request is made for an extent allocation that cannot be found in one free contiguous extent even though the total free space in the tablespace may be significantly larger than the requested size. This is a free space fragmentation problem, and normally the term tablespace fragmentation is referring to free space fragmentation within a tablespace.

How much of a problem free space fragmentation is depends on your tablespace extent management policies. From the first paragraph it should be obvious that sizing the object extents based on reasonable expectations or history and setting the pctincrease for all objects to zero from the default of 50% would help reduce fragmentation, as would the use of the same next extent size for all objects in a tablespace. With Oracle 8.1 the rdbms introduced new tablespace creation parameters that enforce uniform extent sizing within a tablespace overriding any object storage clause specified at creation to pretty much enforce the ideas I just described.

Ver. 8.1
	create tablespace x
	datafile '/dev/vx/rdsk/filename'  size 1024M
	extent management local uniform size 512K;

There are several variations of the options available to manage a tablespace. This FAQ is not going to attempt to cover any of these features in depth, but basically you can now create a tablespace to be dictionary managed, which is the pre-8.1 method or to be locally managed. Locally managed tablespaces contain a bitmap used to control extent allocation within the tablespace and eliminate the need to acquire the single database wide ST lock to allocate or deallocate extents within the locally managed tablespace. Locally managed tablespaces should be declared to use uniform extent management; otherwise, they default to type autoallocate, which forbids users from specifying extent size information. With uniform extents if a user submits an object create with an initial size of 750K in the tablespace above the Oracle rdbms would allocate two 512k extents to hold the object overriding the storage request but would otherwise accept the statement.

One of the nice effects of using uniform extents is that it makes predicting growth and determining when another file needs to be added to a tablespace fairly straightforward. Just divide the total free space in the tablespace by the extent size and round down to the integer value as there are no unusable free extents cluttering up the tablespace. There will usually be some wasted space at the end of the file as the extent size will rarely divide perfectly into the file size minus the header block minus the bitmap remaining useable size, but barring using a very small uniform extent size with humongous objects requiring the creation of multiple bitmaps this will be less than one extent. When measured over time you can get a pretty good idea of extents per time period worth of usage.

If you are working with a pre-8.1 version of Oracle or have inherited a system upgraded from earlier versions that use permanent tablespaces created using traditional dictionary managed space then you can still manage by extents, but you will have to work to get the tablespace objects sized appropriately.

Pre 8.1
	Create tablespace x
	Datafile '/dev/vx/rdsk/filename' size 1024M
	Default storage (initial 512k next 512k pctincrease 0)

The second create statement appears to pretty much define the tablespace the same as the uniform extent example, but if a user submits an object create with a storage clause initial extent request of 750K then Oracle will give them the 750K request overriding the tablespace defaults. Starting with version 7.3 Oracle provided the minimum extent clause, which required that every extent in the tablespace be at least integer value in size or a multiple of this size. In a way this was the forerunner of the new uniform extent option. This parameter should not be confused with the minextents object storage clause parameter.

Here is SQL that works for version 7.3+ (due to inline view) to determine the number of next extent allocations that can be taken within a tablespace based on the largest next extent allocation request size for any object allocated to the tablespace:

COLUMN tablespace_name    FORMAT a20   heading 'Tablespace Name'
COLUMN extents            FORMAT 99999 heading 'Available|Extents'

select   f.tablespace_name
        ,sum(floor(nvl(f.bytes,0)/(s.MEXT))) extents
from     sys.dba_free_space   f
        ,( select   tablespace_name, max(next_extent) as MEXT
           from     sys.dba_segments
           group by tablespace_name
         ) s
where    f.tablespace_name = s.tablespace_name(+)
group by f.tablespace_name

Tablespace Name        Extents
==================== =========
LGDATA01                     6
LGDATA02                     7
LGIDX01                     16
LGIDX02                     32

The packaged procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL can be used to migrate an existing tablespace to being locally managed; however, there is no way to automatically migrate the existing objects extents into a uniform extent size. A note on Metalink advised that the Oracle release level should be before using the routine due to several small bugs (Ref: Oracle, Helen Schoone 23 Aug-01 18:37 Re : Locally managed tablespace question). My personal opinion is that you should build new locally managed tablespaces using uniform extents and migrate your objects over time if at all possible rather than use the package. I believe this because there should not be an ST lock contention problem on systems that have configured a true temporary tablespace to handle sort requests, and this is how sort space should be allocated with 8.1.7+

Further reading: The Concepts manual has an introduction to tablespace management, and the SQL manual documents the create tablespace command. The FAQ: How do I find out who is currently using the space in the temporary tablespace ? has material on the use of temporary vs. permanent tablespaces to support sort operations.

Reference also Oracle Metalink notes: 
	109630.1: How and Where Bitmaps are Allocated for Locally Managed Tablespaces, 
	120061.1: Next Extent Size After Migrating Tablespace from Dictionary to Locally Managed

Back to top

Back to index of questions