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.
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.