Show TOC

 Tables/Index Analysis (Oracle)Locate this document in the navigation structure

Use

You can use this procedure to analyze tables and indexes.

Procedure

Choose Tools →Administration →Computing Center →Management System →Control →Performance Menu →Database →Tables / Indexes.

Alternatively, use transaction code DB02.

In the Tables and Indexes section there are extensive options available for analyzing tables/indexes. Some of these options are described below:

  • Detailed analysis

    Limit the number of tables to be analyzed. If, for example, you only enter the name of the tablespace as the selection criterion, the analysis can take a very long time depending on the number of objects contained in the tablespace.

    Choose Analysis to examine one of the tables listed in more detail.

    • Tables and its indexes: displays the table and the indexes defined for the table. Alongside the size of the objects (in Kilobytes and blocks), the number of used extents and the value defined for the object for MAXEXTENTS are also displayed. The DBA should check these details on a regular basis to avoid any possible Problems with Maximum Number of Extents (Oracle). You can use the Storage Parameter pushbutton to display additional storage parameters for the individual objects.
    • Extents: here you will find the size (in Kilobytes and blocks) of the extents of the table, and their location (file ID, block number).
    • Detail Analysis:.
    • History: displays the history of a table. The DBA can trace the growth of a table over a particular time period. Changes to size and extent assignment, for example, are recorded. The value for the NEXT storage parameter is also listed. This helps the DBA to monitor the situation in storage-critical tablespaces, that is, to determine whether a next extent of this size will fit into the freespace of a tablespace.
    • Table columns: displays the structure of the table in the SAP ABAP Dictionary and in the database.
  • Missing indexes

    See Missing Indexes

  • Space critical objects

    Displays critical objects for the storage space situation.

  • Space statistics

    Displays the history of tables/indexes. Limit the number of tables/indexes to be analyzed as best you can. If, for example, you only enter the name of the tablespace as the selection criterion, the analysis can take a very long time depending on the number of objects contained in the tablespace.

    The DBA can trace the growth of a table over a particular time period. Changes to size and extent assignment, for example, are recorded. The value for the NEXT storage parameter is also listed. This helps the DBA to monitor the situation in storage-critical tablespaces, that is, to determine whether a next extent of this size will fit into the freespace of a tablespace.

See also:

Checking for Freespace Problems (Oracle)

Storage Management Errors (Oracle)

Checking Storage Parameters (Oracle)

Monitoring Table and Index Fragmentation (Oracle)

Tablespace Analysis (Oracle)

Extent Analysis (Oracle)