Try OpenEdge Now
skip to main content
Database Administration
Maintaining and Monitoring Your Database : Managing Performance : Index use : Analyzing index use
 
Analyzing index use
Use PROUTIL IDXANALYS to get information about index blocks and usage, as shown:
proutil db-name -C idxanalys
The IDXANALYS qualifier provides:
*The number of fields and levels in each index
*The size of each index, in blocks and in bytes
*The percent utilization within the index (that is, the degree of disk space efficiency)
*A factor value that indicates whether to rebuild each index
*A summary of indexes for the current database and the percentage of total index space used by each index
Note: You can run PROUTIL IDXANALYS while the database is in use; however, PROUTIL generates only approximate information.
The most important field in the IDXANALYS display is the % Util field. This field shows the degree of consolidation of each index. If an index is several hundred blocks and your application most frequently retrieves data, an index utilization of 85 percent or higher is optimal. There are two ways to increase an index's utilization rate:
*Compress the index with the database online or offline with the PROUTIL IDXCOMPACT utility
*Rebuild and compress the index offline with the PROUTIL IDXBUILD utility
The Levels field shows the number of reads PROUTIL performs in each index per entry. The Blocks and Bytes fields show you the size of each index. The Factor field is based on the utilization and size of the index; it is an indicator of when you should rebuild indexes. The table below provides a description of the different ranges of values for the Factor field. When you use the Factor field to decide whether to rebuild an index, consider the context of how the particular index is used. For example, if an index is highly active, with continuous insertions and deletions, its utilization rate varies greatly, and a rebuild is inadvisable. However, a static index with a high factor value benefits from a rebuild.
Table 49. Factor values
Factor range
Description
1 to 2
The index is well-utilized and balanced. You do not have to rebuild it.
2 to 2.5
The index is less than 50 percent utilized and/or the index is unbalanced. You should consider a rebuild.
2.5 to 3
The index is less than 25 percent utilized and/or the index is very unbalanced. You should rebuild this index.