Reorganization with SAPDBA (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Reorganization with SAPDBA

Use

You can use SAPDBA for Oracle to reorganize the database. Reorganization improves the structure of the database, and can result in improved performance.

Prerequisites

When installed for an SAP System, the Oracle database looks as follows:

Most of the tables and indexes of a tablespace are stored in only one extent.
Each tablespace consists of exactly one data file.

This initial database status can change as follows:

Additional extents

When more space for extra data is required, additional extents are created for the tables and indexes of a tablespace. The result might be poorer data access times.

Additional data files

When a tablespace is full – that, is, there is not enough freespace to create a new extent – additional data files must be added (except if the AUTOEXTEND option is used).

Freespace fragmentation

Adding or deleting complete objects causes freespace fragmentation in a tablespace. Free storage space in data files is divided into smaller units. If these are smaller than one requested extent, the space is lost and cannot be used for storing data.

The Oracle system now automatically merges adjacent areas of free space, so this problem is less likely to occur than in the past.

Internal fragmentation

This occurs if the fill level of the database blocks develops unevenly. The fill level of the individual blocks is initially identical. Creating and deleting data causes some blocks to be filled completely, while others remain relatively empty. As a result, space is used inefficiently.

Block chaining

If a data record does not fit into a database block, block chaining occurs. When the record is accessed, the system must then follow a chain from the first block of the data record to the further blocks. As a result, more time is needed for reading data from the disk.

Since SAP Systems usually access table entries using an index, the above changes to the database do not normally increase the time required to access data. However, such changes can increase run times for full-table scans.

Effects of a Reorganization

A reorganization can have the following positive effects on the database:

The data from one object is merged into a single extent.
The data from one tablespace is merged into one or more data files.
Freespace fragments in an object are merged into larger freespace segments, and this process is called "defragmentation".
The fill level in the individual blocks is evened out, so reducing internal fragmentation.
Data chains are resolved.

Features

Reorganize various objects, such as, tables, indexes, lists of tables or indexes, tablespaces, tablespaces with data files

Move or rename of the data files in a tablespace

Resize the data files in a tablespace

Restart or reset (that is, repeat from the start) an interrupted reorganization

Change storage parameters, such as initial extent, for all reorganization types

Note

SAPDBA supports reorganization of partitioned tables and indexes. The reorganization does not change the partitions and their parameters.

If a partition of a partitioned table or index is in a tablespace that you want to reorganize, SAPDBA reorganizes all other partitions of the object in other tablespaces too, even if you do not specify that you want to reorganize the other tablespaces. In other words, SAPDBA reorganizes all partitions of a partitioned object.

SAPDBA also supports the reorganization of tables with all types of large object (LOB) columns. Large objects are recreated with the same physical characteristics as before the reorganization.

Activities

You prepare for the reorganization, including deciding if it is really necessary.

If required, you change storage parameters without reorganizing.

If necessary, you reorganize the database.