Command Option -analyze (SAP Library - Database Administration (Oracle) with SAPDBA)

Command Option -analyze

The command option sapdba -analyze <tablespace(s)|table> involves an analysis of the allocated and actually used space for the specified table or tables and/or indexes in the specified tablespaces. (Normally, if you have kept to the SAP naming convention, there should be no indexes in a data tablespace and no tables in an index tablespace. In this case, statistics are only generated for the one object type. If there are tablespaces, which contain various object types, statistics are generated both for the tables and the indexes per tablespace).

Based on an estimate, the command:

Specifies the estimated freespace per specified tablespace.

Tablespace structure: tablespace_name, total_space, free, allocated, used

Creates a hit list of the 20 tables and/or indexes with the most unnecessarily allocated space.

Table structure: tablespace_name, table_name, empty, never_been_used, used
Index structure: tablespace_name , index_name , total , used_by_btree , used

When you call sapdba -analyze (without defining a method/option), the ORACLE command ESTIMATE is applied to the relevant tables. You can also perform analyses using COMPUTE or VALIDATE STRUCTURE (option sapdba -analyze <TSP> -method <options> ). Because this analysis is quite time-intensive (depending on the number of tables or indexes involved), it should be planned in the background and, if possible, only for selected tablespaces or tables. An analysis of all tablespaces for an SAP database with 5 GB of data can take up to 5 hours, for example, depending on the hardware used.

You can limit the number of records to be analyzed by using the option sapdba -analyze <TSP> -option R<n>|P<n(*1000)>:
P<n>: n percent of all the existing records are analyzed,
R<n(*1000)>: n*1000 records are analyzed.

You can decide whether you want to create current statistics with SAPDBA or use existing statistics which are not older than a certain number of days. Use the option
sapdba -analyze <TSP> -time <number of days> ).

To limit long analysis operations to a certain number of hours, use the option
sapdba -analyze <TSP> -T <number of hours> .

The values determined by the analysis can be used as the basis for manually reducing the storage sizes of tables/indexes or tablespaces. You can reduce their space requirements during the reorganization, for example by calling the reorganization with menu option Change storage manually yes to possibly reduce the value for INITIAL in accordance with the analysis. However, we always recommend a safety margin of 10-20%.

Normally you will not have to perform this complex, manual procedure. If you choose the Reduce object size: yes option during the reorganization, the analysis will be performed directly during the reorganization and the space requirements will be reduced automatically. SAPDBA takes the safety margin described above into consideration.

See Reducing the Size of the Table or Index.

When tables with a large data volume are involved, the temporary tablespace PSAPTEMP may overflow, since the analysis with sapdba -analyze involves a large number of sort operations. No statistic values are available for this table and the storage space which was assigned is not reduced.

The results of the analysis are stored in the log sapcheck/<timestamp>.aly .

The command option -analyze or a reorganization with the Reduce object size: yes option refreshes the database statistics of the particular object and thus has an effect on the behavior of the database optimizer (see Support of the Cost-based Optimizer). Also note that the statistics of all the reorganized objects are deleted during reorganization. You can recreate them with the command option -analyze or with the menu option Check extents and fragmentation.