Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Fragmentation and Reorganization

Fragmentation is a problem that can negatively impact performance—and one that many DBAs struggle to manage. Fragmentation can be an unwanted phenomenon if it results in small parts of noncontiguous “free space” that cannot be reused.

There are many types of fragmentation:

• Segment fragmentation (i.e., “holes” in segments consisting of multiple extents)

• Tablespace free space fragmentation (i.e., chunks of free space between a segment’s extents)

• Row fragmentation (i.e., a row residing in multiple blocks)

• Index leaf block fragmentation (i.e., leaf blocks with holes)

• Data block fragmentation (i.e., data blocks with holes)

This section discusses some common misunderstandings about fragmentation and offers some tips on how to minimize the fragmentation issues on which DBAs typically focus: segment fragmentation, free space fragmentation, and index fragmentation.

Segments and Multiple Extents

As mentioned in Chapter 2, Oracle manages disk space as a collection of datafiles (grouped into tablespaces) that represent the disk space Oracle can utilize. 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 ...