Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

5.4 Fragmentation and Reorganization

Fragmentation
is a problem that can negatively impact performanceand one
that many DBAs have struggled to manage. 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 spacefor 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, performance is negatively impacted.

5.4.1 Resolving Fragmentation

In Oracle Database 10g, resolving
fragmentation becomes fairly trivial. You can perform an online
segment shrink using the Segment Advisor interface accessible through
EM. ADDM recommends segments to shrink, and you simply choose to
accept the recommendations.

Prior to Oracle Database 10g, the common means of reducing
fragmentation in Oracle9i was through an online
reorganization accomplished through a
CREATE TABLE . . .
AS SELECT online operationthat is, the copying of the contents
of one table to another while the original table is updated. Changes
to the original table were tracked and applied to the new table.
Physical and logical attributes of the table could be changed during
this online operation, thus allowing an online reorganization.

Compare and contrast these methods to reducing fragmentation in
Oracle database versions prior to Oracle9i. The
general recommendation was to avoid fragmentation through careful
planning. But the usual way to solve fragmentation was to reorganize
a table by exporting the table, dropping it, and importing it. The
data was unavailable while the table was in the process of being
reorganized. Many DBAs claimed that they saw improved performance
after reorganizing segments into a single extent. Over time, a
decrease in performance reoccurred as the number of extents the table
occupied increased.

Oracle performance increased as a result of
these reorganization operations, but this improvement was
not due to a decrease in the number of extents.
When a table is dropped and recreated, several things happened that
increased performance:

Each block was loaded as full of rows as possible.

As a consequence, the high-water mark of the table (the highest block
that has ever had data in it) was set to its lowest point.

All indexes on the table were rebuilt, which meant that the index
blocks were as full as possible. The depth of the index, which
determined the number of I/Os it takes to get to the leaf blocks or
the index, was sometimes minimized.

By eliminating fragments and shrinking segments in a much more
automated and online fashion, Oracle Database 10g greatly simplifies
solving fragmentation problems; the result is that optimal conditions
exist for performance.