Progress
Database Administration
Guide and Reference


Analyzing Index Use

Use PROUTIL’s IDXANALYS qualifier to get information about index blocks and utilization.

To execute the IDXANALYS qualifier, enter the following command:

proutil db-name -C idxanalys 

db-name

Specifies the name of the database.

The IDXANALYS qualifier provides:

NOTE: You can run PROUTIL with the IDXANALYS qualifier while the database is in use; however, PROUTIL generates only approximate information.

The most important field in the IXANALYS 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:

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. Table 14–3 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 14–3: 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.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095