Space Management   «Prev  Next»

Lesson 11Oracle Fragmentation
ObjectiveImpact of Oracle tablespace fragmentation.

Oracle Tablespace Fragmentation

Extents and segments let you control the way Oracle stores data, while still optimizing storage. The interaction between different extents within a tablespace can create a problem that can impact performance.

Tablespace fragmentation

A tablespace can contain many different segments for different database objects. Each of these extents can be a different size. Extents are added to a tablespace as additional storage space for each database object is needed. Extents can also be dropped from a tablespace, as when their database objects are dropped. This can lead to a condition know as tablespace fragmentation, where a tablespace contains holes that cannot be reused for new extents. The following set of diagrams show how this type of fragmentation can occur.

Oracle Tablespace Fragmentation

1) This tablespace contains two database objects, one with an extent size of 500 KB and another with an extent size of 1MB.
1) This tablespace contains two database objects, one with an extent size of 500 KB and another with an extent size of 1MB.

2) As extents are added to the tablespace, the different size extents are intermixed.
2) As extents are added to the tablespace, the different size extents are intermixed.

3) If one of the smaller extents is dropped, it leaves a hole of 500KB. The larger extent cannot fit into the smaller extent space, because extent space must be contiguous. Consequently, this 500-KB hole remains in the tablespace.
3) If one of the smaller extents is dropped, it leaves a hole of 500KB. The larger extent cannot fit into the smaller extent space, because extent space must be contiguous. Consequently, this 500-KB hole remains in the tablespace.

  1. This tablespace contains two database objects, one with an extent size of 500 KB and another with an extent size of 1MB.
  2. As extents are added to the tablespace, the different size extents are intermixed.
  3. If one of the smaller extents is dropped, it leaves a hole of 500KB. The larger extent cannot fit into the smaller extent space, because extent space must be contiguous.

TableSpace Database Objects

Effect of Fragmentation

The overall effect of this fragmentation is twofold:
  1. Your Oracle database will require more storage space, because the tablespace contains a large portion of wasted space, and
  2. accessing data within the fragmented tablespace will take longer, because the larger tablespace will require more disk head movement.

Diagnosing tablespace fragmentation

The problem of tablespace fragmentation only occurs as your tablespace is used over time. How will you know when your tablespaces have become fragmented?
You can use a variety of queries against the data dictionary views that report on space usage in the tablespace, but one of the easiest ways to determine whether you have a tablespace fragmentation problem is to use the Tablespace Manager. The Tablespace Manager is a part of the Add-on Tuning Pack for Oracle Enterprise Manager.

Fragmentation and Reorganization

Fragmentation is a problem that can negatively impact performance and one that many DBAs have struggled to manage in the past. Fragmentation can be an unwanted phenomenon if it results in small parts of noncontiguous "free space" that cannot be reused. In Oracle, a collection of contiguous blocks is referred to as an extent. A collection of extents is referred to as a segment. Segments can contain anything that takes up space. For example, a table, an index, or a rollback segment. Segments typically consist of multiple extents. As one extent fills up, a segment begins to use another extent. As fragmentation occurs, by database activity that leaves "holes" in the contiguous space represented by extents, segments acquire additional extents. As fragmentation grows, increased I/O activity results in reduced performance.

Add-on Tuning Pack:

One of any number of applets Oracle offers to extend the capabilities of the Oracle Enterprise Manager.
The next lesson shows how to avoid and fix fragmentation.