Managing Tables and Indexes (Oracle)

Use

To avoid fragmentation, you must manage tables and indexes in your Oracle database. An object (table or index) is fragmented if it has a high number of extents allocated or if it has a lot of unused space in the allocated blocks. A high number of extents is sometimes called “external fragmentation”, while unused space is sometimes called “internal fragmentation”. Fragmented objects can threaten continuous availability of the system by reducing performance, so leading to downtime for reorganization.

For data storage, the Oracle database allocates extents to objects (tables or indexes), as shown in the diagram below.

Oracle Storage Allocation

In Oracle 8 there is a limit for the maximum number of extents that one object can have. This limit is determined by a parameter called MAXEXTENTS in the storage definition of an object. MAXEXTENTS is usually set to a value of 300 or 505. The limit can be altered without downtime, so allowing processing to continue against the affected object.

Up until Oracle 7.2 there was a hard limit for the number of extents that depended on the database block size, but this limit has now been removed.

Prerequisites

Fragmentation has the following effects:

·        Table fragmentation

This does not usually cause performance degradation and does not require a reorganization, because the SAP system has been optimized to use indexes. Even tables with very many extents (for example, 100) might not suffer performance degradation. However, this depends on how the table is accessed in practice. For example, a read of one record using a unique key and an index is not affected by the number of table extents. However, a read of a range of records might be affected, even if an index is used.

·        Index fragmentation

A fragmented index can potentially degrade database performance to the extent that reorganization is necessary, so incurring downtime. Heavily used indexes might already cause problems with only 20 extents, whereas others do not. It is not possible to give a general rule. Causes for index fragmentation might be improper index storage parameters and frequent insert, update and delete operations.

You need to reorganize an index if the index is heavily used and fragmented. Fragmentation can be a high number of extents or a lot of unused space in the allocated blocks (this happens if many insert operations in a table are followed by many delete operations).

Procedure

...

       1.      Regularly monitor the objects in your database using BR*Tools, as follows:

Ў        Monitor database objects (especially indexes but also tables) for the number of allocated extents. BRSPACE provides a function to list all object extents.

Ў        Monitor the database for objects with excessive unused space. You can do this with BRSPACE or the database tools supplied by Oracle, as described below. See Checking for Fragmentation (Oracle) for a detailed description or refer to the BRSPACE documentation.

You can also use the Computing Center Management System (CCMS) to monitor the number of extents.

       2.      Make sure that the extent size parameter NEXT is configured properly.

You can also use BRCONNECT to adjust the storage parameter next, which defines the size of the next extent to be created for objects with brconnect -next. Increasing the value of nextresults in the allocation of fewer but larger extents.

For more information, see Adapt Next Extents with BRCONNECT.

       3.      To raise the limit for the maximum number of extents that an object can have, use the command alter table <table_name> storage (maxextents xx) where xx is the new limit for the maximum number of extents.

The limit can either be a number – for example, 2000 – or the keyword unlimited. Too high a value can lead to undesirable fragmentation. Currently you cannot use unlimited for rollback segments, and SAP recommends that you do not use it with other object types (such as tables, indexes, and so on).

       4.      If the object (table or index) has a high number of extents, use BRSPACE to reorganize the object.

For a table, you can use BRCONNECT to adjust the NEXT parameter and BRSPACE to perform the reorganization. For the reorganization of an index, extra disk space (in PSAPTEMP) is needed for index building (roughly 200% of the largest index to be reorganized, not the size of the index tablespace). Index reorganization is faster than table reorganization because the index is dropped and recreated whereas tables are exported and imported.

Reorganize at individual object level rather than at tablespace level

It is better to reorganize single tables or indexes rather than entire tablespaces. If you are closely monitoring these objects with the CCMS or BRSPACE, you can soon spot where problems are arising. For more information about reorganizations, see Reorganizing Objects (Oracle).

Result

You avoid fragmentation and so minimize downtime for your Oracle database and the SAP system.