Deciding if a Reorganization is Necessary (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Deciding if a Reorganization is Necessary

Use

Reorganization of an Oracle database is not required as often as you might think.

Prerequisites

You have completed:

Checking Statistics for a Reorganization

Checking Extents and Fragmentation

Procedure

Consider the following when deciding if a reorganization is necessary:

Number of data files

A reorganization including data files is not necessary until the total number of data files in the database approaches the limit MAXDATAFILES . In such cases, reorganize tablespaces that consist of many files using the SAPDBA option Reorganization ® Reorganize tablespace and data files.

Number of extents per table or index

The SAP System normally uses indexes to access records. This means that full table scans are only rarely required. Therefore, it is not necessary to limit the number of extents per table as long as Oracle is capable of managing that number.

Only consider a reorganization when total system performance declines due to a table or an index with a large number of extents. If many of the objects consist of a large number of extents, reorganize the relevant table, index, or tablespace.

Number of freespace fragments in the tablespace

The system monitor of the Oracle database system is capable of automatically merging the contiguous freespace areas. This significantly reduces the probability that a reorganization becomes necessary due to freespace problems.

However, if none of the remaining fragments is large enough to create a new extent, extend the tablespace and possibly schedule a tablespace reorganization at a later date.