Show TOC

Reorganizing Objects (Oracle)Locate this document in the navigation structure

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):

Figure 1: Types or Reorganization with Oracle
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 runtime.

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 one hour 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 if an offline reorganization is used.

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

    Temporarily set DBMS parameters to maximize performance during reorganization (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 table reorganization and index rebuilding.

    If you have a multi-processor machine, you can use parallel processing 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.

More Information