Reorganizing Objects (Oracle)

Use

The following reasons might necessitate reorganization of the objects in your Oracle database:

·        A tablespace might soon overflow and you decide to reorganize it rather than add a new data file.

·        A database object might be severely fragmented.

Be sure to regularly monitor the database using the Computing Center Management System (CCMS) in the SAP system or BRSPACE, so that you can anticipate problems requiring reorganization before they cause unplanned downtime. 

The following diagram illustrates the kinds of reorganization possible (there are three data files involved in the reorganization, containing various extents from two tables):

Oracle: Reorganization Types

Prerequisites

When you decide to perform a reorganization, there are a large number of factors to consider if you want to minimize the downtime (note that some of the recommendations given below are performance-related since improving the speed of a reorganization can reduce downtime).

The duration of data reorganization depends on the database size and the objects to be reorganized and could take several hours. Index reorganizations are usually faster than table reorganizations. For example, the reorganization of an index of about 400 MB in size might take less than 10 minutes while for a 400 MB table around two hours might be needed.

Procedure

This procedure consists of recommendations about when and how to reorganize.

...

       1.      Reorganize only when necessary.

Monitor the system closely and only reorganize when necessary. It is far better to reorganize individual tables and indexes rather than entire tablespaces. You can do online table reorganization with the SAP system running.

       2.      Reorganize early when database is small.

Do reorganizations early while the database is smaller. This can be done based on early usage patterns and future projection. For example, perform reorganizations immediately after an upgrade.

       3.      Use EarlyWatch to monitor storage parameters.

One way of monitoring the system is to use the EarlyWatch service to detect storage problems early. EarlyWatch also gives recommendations to adjust parameters, so helping to avoid reorganization completely.

For more information, see SAP Safeguarding.

       4.      Schedule reorganizations alongside backups.

It is a good idea to back up tablespaces before reorganization. Therefore it makes sense to schedule reorganizations immediately after regular backups to avoid the need for a separate backup. It is also a good idea to batch a number of reorganizations together to reduce total downtime.

       5.      Prepare properly for reorganization.

Check using BRSPACE to make sure all required disk space/operating system files are available and pertinent parameters are set properly before starting the reorganization. This avoids unnecessary downtime due to improper settings or insufficient resources.

       6.      Maximize performance during reorganization to minimize downtime.

Temporarily set DBMS parameters to maximize reorganization performance during shutdown (for example, increase sort buffers, increase block I/O sizes). For more information about detailed parameter settings, refer to the Oracle documentation. Maximizing reorganization performance is useful because DBMS parameters configured for production time are rarely optimized for data reorganization. The reverse is also true, which means it is equally important to restore the DBMS parameters to their original values after reorganization is complete.

       7.      Use parallel index building if possible.

If you have a multi-processor machine, use parallel index building to reduce reorganization time.

Result

Reorganization helps you to solve problems that might lead to downtime. However, be sure to continue monitoring the database so that you can anticipate future problems before they cause downtime.

See also:

Segment Management with BR*Tools