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)

Monitoring Calls (Oracle)

Monitoring Table Access Methods (Oracle)

Table Scans: Problem Analysis (Oracle)