Space Analyst for Oracle - Indexes Tab

From DBArtisan
Jump to: navigation, search

Go Up to Using Space Analyst

The Indexes tab displays diagnostic information regarding index space inefficiencies that may exist in your database.

The Index Reorganization Candidate Summary section uses a bar chart to count the various types of index space inefficiencies in your database. Note that the reorganization threshold options are used to compute the counts for each type of space inefficiency.

The table below describes the three distinct types of table space inefficiencies that Space Analyst computes:

Problem Description

BLevel

These indexes have extended beyond the allowable B-level size set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Indexes with extended levels can take longer to scan and can therefore contribute to slower response times.

Next Extent Problems

These indexes cannot obtain their next extent of free space due to a lack of needed contiguous free space in the tablespace. Indexes suffering from this problem may cause space-related errors if more data is added to their parent table. This problem will be highlighted even if the tablespace that contains the table has its autoextend property for one or more datafiles set to true.

Max Extent Problems

These indexes are nearing their maximum extent limit and have exceeded the allowable maximum extent percentage set in the customizable thresholds of Space Analyst (to see all the thresholds, click the toolbar option labeled Reorganization Threshold Options). Indexes suffering from this problem may cause space-related errors to result if more data is added to their parent table.

The Index Reorganization Candidate Details section provides filters and a grid that you can use to view detailed space diagnostics for all or selected indexes. The grid contains the following data:

Data Description

Index Owner

The owner of the index.

Index Name

The name of the index.

Index Type

Indicates if the type of index (index, index partition, etc.)

Index (KB)

The total allocated space of the index in KB.

Number of Rows

The number of rows that the parent table contains.

Note: This column may report NULL if the table has not been analyzed via the ANALYZE command or through the DBMS_STATS package.

Clustering Factor

Indicates the amount of order of the rows in the table based on the values of the index. If the value is near the number of blocks, then the table is very well ordered. If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.

Index Blevel

Indicates the level of the index. Numbers beyond 3-4 can indicate a fragmented index.

Number of Blocks

The total number of blocks allocated to the index. Note: this column may report NULL if the index has not been analyzed via the ANALYZE command or through the DBMS_STATS package.

Max Extent Percent

A percentage indicating how close an object is to reaching its maximum extent limit. High percentages indicate indexes that may be candidates for reorganization.

Number of Extents

The number of extents that the index consumes.

Max Extents

The maximum extent limit for the index.

Can Extent Space

Indicates if the object can allocate its next extent. If 'N', this is because the tablespace does not currently have enough contiguous free space. This problem will be highlighted even if the tablespace has its autoextend property for one or more datafiles set to true.

Next Extent

The next extent (in bytes) that the index will attempt to allocate when more space for the index is required.

Max Free Space

The largest contiguous block of free space that exists in the tablespace where the index resides.

Tablespace Name

The tablespace that contains the index.

Note: The grid can also contain a column for deleted leaf rows if the Get Deleted Leaf Row Statistics option has been selected. Be aware that the calculation of this statistic can take a lot of time for databases with large numbers of indexes. Large numbers of deleted leaf rows can indicate an index in need of reorganization.

Available Functionality

The following functionality is available in the Command menu, shortcut menu, and toolbar:

Note: Functionality differ by platform.

Topics

Retrieved from "https://docwiki.embarcadero.com/DBArtisan/171/e/index.php?title=Space_Analyst_for_Oracle_-_Indexes_Tab&oldid=5046"