Changing the Extent Size (SAP Library - Database Administration (Oracle) with SAPDBA)

Changing the Extent Size

With all reorganization types, you can change the storage parameters (i.e. influence the extent parameters, among others) with the menu items Compress extents, Use ABAP/4 DIC for NEXT, Reduce object size and Change storage manually.

INITIAL

It makes sense to change the size of the INITIAL extent in the following cases:

Larger extent: If you expect a table or index to grow quickly, you can specify a larger value for INITIAL.

The additional space in the extent enables the database system to add data without having to immediately create a new extent. When possible, define the extent large enough to cover the requirements of the table or index for some time.

Remember that the extent will have to fit in the freespace expected after a reorganization. SAPDBA undertakes this check before the start of the reorganization.

Useful information for determining the extent size:

Information about growth trends in the SAP Performance Monitor
See online help for the Computing Center Management System.

Freespace information in SAPDBA (Tablespace administration ® Freespace and fragmentation of all tablespaces, see Freespace Statistics).

Smaller extent: When the following conditions apply, you can specify a smaller value for INITIAL:

The option Reduce object size: yes was not used.
You expect a table or index to shrink because data has been deleted.
Only little new data is expected.

SAPDBA always performs the space check both for the first extents according to the value of MINEXTENTS, and for all other NEXT extents expected during the data import (from the total size of the object).

MINEXTENTS

The minimum number of extents per object should normally be 1. SAPDBA however divides the given INITIAL extent (when Compress extents: yes) into several parts, if INITIAL would be bigger than the biggest data file or the largest available freespace fragment. In this case, SAPDBA also sets the MINEXTENTS to the corresponding number.

MAXEXTENTS

The maximum number of extents per object should be 100 (with a block size of 8 KB).

NEXT

The size of the NEXT parameter depends on the expected growth of the individual objects. Also consider the selected INITIAL value. If a tablespace contains many fast-growing objects, you should set NEXT large enough to cover storage requirements in the long term and avoid creating new extents whenever possible.

Also learn about the SAPDBA command options -analyze and -next , which can help you decide if and when to change the extent sizes, and even change the NEXT value automatically, when necessary.

See Command Option -analyze and Command Option -next.

For more information on the other storage parameters, please refer to Changing Storage Parameters.