Extent Check Functions (SAP Library - Database Administration (Oracle) with SAPDBA)

Extent Check Functions

With its menu item Check extents and fragmentation in the Reorganization menu, SAPDBA provides the following check functions:

Objects with at least n extents displays objects that may need to be reorganized due to their large number of extents.

This function checks all objects in the database.

You can restrict the search by specifying a list of tablespaces, tables, indexes or other segment types to check.

Lists must have the following format: Name,Name,... .
Use the ORACLE placeholder "%" for generic specifications (corresponds to the ORACLE LIKE function). All objects with this LIKE pattern are checked.

Special features when selecting the tablespaces: You would generate a list of tablespaces (PSAPBTABD and PSAPBTABI) with PSAPBTAB%. You could select a tablespace from this list. If you want to check both the objects of PSAPBTABD and the objects of PSAPBTABI, you can do so by entering PSAPBTAB%%.

Entering "all" means that you select all objects.

Tablespace fragmentation (summary) displays information on the size and fragmentation level of the specified tablespace.

The following information is displayed:

Number of objects (tables and indexes) in the tablespace
Number of allocated extents
Average number of allocated extents per object
Number of data files in the tablespaces
Number of free fragments
Number of freespace areas
Average number of free fragments per data file

Allocated space (total of all allocated extents)
Freespace (total of all free fragments)
Largest freespace area of the tablespace (adjacent free fragments)

Available storage space in the tablespace
Space occupied by ORACLE and operating system overhead
Total size of the tablespace

Tablespace fragmentation (all extents) displays detailed information about all segments (tables, indexes, ...) and their extents of the selected tablespace. The extents appear in the table in accordance with their physical sequence within the data files. If you enter an object name or ‘FREE’, it is also possible to display the physical position and fragmentation of the object or only of the freespace.

You can use the first check function listed above to search the database, the second to display detailed information about the tablespaces that have a large number of extents, and the third to display an overview of the distribution of the files in the individual extents (e.g. the fragmentation).

Estimate/Compute table
Validate index
Estimate/Compute tablespace

These options enable you to contrast the allocated and actually occupied space, based on the ORACLE function ANALYZE TABLE/INDEX <name> ESTIMATE/COMPUTE STATISTICS or ANALYZE INDEX <name> VALIDATE STRUCTURE. The runtimes of an analysis (especially when COMPUTE is used for all tables/indexes of a tablespace) can be quite long. You can also limit the time with the menu (see: Command Option -analyze).
You can analyze an individual table (Estimate/Compute table), an index (Validate index) or all objects (tables and indexes) of a tablespace (Estimate/Compute tablespace).

You can generate current statistics each time or go back to statistics that already exist (Get last analysis).

The values can be used as a starting point for reducing storage sizes of tables/indexes and/or tablespaces during a reorganization. Please note, however, that a safety margin of 10-20% is always recommended. This safety margin is automatically taken into consideration when you reorganize with the Reduce object size: yes option.

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

The function Validate index locks the entire table belonging to the index.

For information on logging all check functions, please refer to Overview of the Logs and Setting the Log Checks Option.