Checking Extents and Fragmentation (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Checking Extents and Fragmentation

Use

Before you reorganize the Oracle database with SAPDBA, we recommend that you check extents and fragmentation for the objects you intend to reorganize. This helps you to decide if a reorganization is necessary.

Prerequisites

You have configured and started SAPDBA. Refer to Getting Started with SAPDBA.

For more information on logging checks, see Setting the Log Checks Option and Log Files and Their Contents.

Procedure

Choose Reorganization ® Check extents and fragmentation.

Choose Objects with at least n extents to display objects that might need to be reorganized due to having a large number of extents.

The default is to check all objects in the database, displaying all that have more than two extents. You can refine the search by entering:

Criteria such as tablespace name
A different threshold for number of extents

Choose Tablespace fragmentation (summary) to display summary information on the size and fragmentation level of the specified tablespace.

To refine the display, enter a tablespace name.

Look at the following values:

Average number of allocated extents per segment

Average number of free fragments per data file

If these values are high, a reorganization is indicated. A value close to 1 is optimal.

Choose Tablespace fragmentation (all extents) to display detailed information on the size and fragmentation level of the selected tablespace.

SAPDBA displays the extents in the table according to their physical sequence within the data files.

Choose Segment, then:

To see information for a single segment, enter its name.

To see information on freespace at segment level, enter free .

To see information on all extents, enter all .

Choose one of the following to contrast allocated and used space in database objects:

Estimate/compute table

Validate index

Estimate/compute tablespace

Caution

Estimate/Compute table/tablespace locks only the table record just examined.

Validate index locks the entire table belonging to the index.

These options are equivalent to the command sapdba –analyze . For more information on the parameters, see sapdba -analyze.

You can use the information displayed as a starting point for reducing storage sizes of tables, indexes, or tablespaces during a reorganization. We recommend that you always leave a safety margin of 10-20% extra space in an object. This safety margin is automatically set when you reorganize with the Reduce object size: yes option.