Monitoring Table and Index Fragmentation
(Oracle)
Fragmentation of tables and indexes may reduce
performance, depending on the way data is accessed. Fragmentation also leads
to greater overall storage space usage. This problem can be eliminated by
reorganizing the particular object. However you should bear in mind that this
process is very expensive and that the system is not available during a
reorganization. It is often not advisable to immediately start a
reorganization to eliminate fragmentation. For more information on when to
perform a reorganization, see
Reorganization.
Table Fragmentation
Table fragmentation will result in longer query times when a full table scan is performed. Since data is not as evenly packed in the data blocks, many blocks may have to be read during a scan to satisfy the query. These blocks may be distributed on various extents. In this case, Oracle must issue recursive calls to locate the address of the next extent in the table to scan.
Recent
studies have shown that table fragmentation has hardly any effect on the
performance of the database system. This is mainly because full table scans
are somewhat rare in an SAP system since data is accessed using an index.
Reorganizing table data is generally not as beneficial to performance as
previously thought. For more information on how to perform a reorganization,
see
Reorganizing Tables
with BR*Tools
Index Fragmentation
Index
fragmentation may bring a higher penalty to application performance. When
accessing data through an index and an index range scan (common in SAP
systems), Oracle must read each block in the specified range to retrieve the
indexed values. If the index is highly fragmented, Oracle may have to search
many more blocks, and possibly levels, to get this information. To eliminate
index fragmentation, you must
rebuild the index.
In both cases – table fragmentation and index fragmentation –always make sure that the soft and hard limits for the number of extents (MAXEXTENTS parameter) are not reached. If this happens, you must intervene.
See also:
Problems with Maximum Number of Extents (Oracle)