Overview of the Storage Parameters (SAP Library - Database Administration (Oracle) with SAPDBA)

Overview of the Storage Parameters

During the reorganization or export, SAPDBA displays all values for the storage parameters in a menu, enabling you to see and change the respective current value, the default value and the new value that was manually changed if necessary. The parameters PCTINCREASE, PCTFREE, PCTUSED, FREELISTS and FREELISTS GROUP should only be changed after consulting SAP (e.g. if recommended by the EarlyWatch Service).

The following parameters exist and can be displayed and - if necessary - changed at the start of a reorganization or export using Change storage manually: yes:

INITIAL: The size of the single extent into which a table or an index will be imported. The default SAPDBA size ( Compress extents: yes) is the total of the occupied extents (INITIAL and NEXT) of a table or an index. If you select Compress extents: no, the old INITIAL value is used. If you choose the option Reduce object size: yes , a smaller value is defaulted if necessary.

Larger value for INITIAL: Specify a larger value for the INITIAL extent of tables/indexes that grow quickly. In this case, the database will not have to immediately assign a new extent.

Smaller value for INITIAL: Specify a smaller value for the INITIAL extent when you know that a table or index does not require the full space allocated.

NEXT: The size of the second and all other extents assigned to a table or an index. Usually ( Use ABAP/4 Dic.for NEXT: yes), this value is calculated using tables DD09L and TGORA/IGORA. See Default Value for NEXT.

You can generally leave the NEXT value set to the SAPDBA default value. If you have made large changes to the INITIAL value, however, you should increase or decrease the value of NEXT accordingly.

MINEXTENTS: Starting number of extents when the table is created (should be set to 1; Exception: The INITIAL extent must be split because of the free space available or the data file size. In this case, SAPDBA changes the value for MINEXTENTS independently).

MAXEXTENTS: Maximum number of extents allowed for the table.

PCTINCREASE: Is always set to zero (0) by SAPDBA (and cannot be changed manually with SAPDBA), to prevent exponential growth of extent sizes in all SAP tables. As a result, NEXT is the size for all new extents assigned after the first one.

You can simulate an asynchronous, linear PCTINCREASE response, however, by using the command option sapdba -next . See Command Option -next.

PCTFREE: This parameter specifies the percentage of storage in a data block that is held empty for possible changes to existing lines (default value: 10%).

PCTUSED: When a data block is full (except for the space for PCTFREE), no new lines are added to that block. No more lines can be added to that block until the percentage of used storage falls below the value of PCTUSED (default value: 40%).

PCTFREE and PCTUSED are used to optimize use of the storage space in the extent data blocks. Both parameters can be specified for tables. For indexes only PCTFREE can be specified.

FREELISTS: Number of free lists for an object for managing the data blocks. The default value is 1. The maximum possible value depends on the database block size. Please only change this value after consulting with an expert or when recommended by the EarlyWatch Service.

FREELIST_GROUPS: Defines the number of groups of freelists which is assigned to an object. The default value is 1. This parameter should only be set to a larger value if you are working with the Parallel Server Option of ORACLE.

The optimal value for FREELIST GROUPS corresponds to the number of database instances for tables. If you are planning to increase the number of database instances, you should use a higher value right from the start. It is only possible to change parameters FREELISTS and FREELIST GROUPS by reorganizing the tables or indexes. They cannot be changed at a later time with a corresponding ALTER command.

For values larger than 5 ORACLE blocks, the values for INITIAL and NEXT are always rounded off to a multiple of 5 ORACLE blocks. (Example: a NEXT value of 6 blocks was defined. In further operations, the system will use a NEXT value of 10 ORACLE blocks.) This procedure is determined by ORACLE and is taken into consideration by SAPDBA (see for example Default Value for NEXT).