How to determine when need dump/load an OpenEdge database?

Two key factors that should be considered when determining if a database should be dumped and loaded, are the factor and the scatter factor. This information can be found by running a dbanalys report against the database:

$ proutil dbname -C tabanalys
Table   Records  Size   Min  Max  Mean  Count     Factor  Scatter Factor  
Totals: 81980271 11.6G    6  3087 152   81988854    1.0     3.6  


Degree of record fragmentation for the table.

This value is determined by the number of fragments divided by the ideal number of fragments (for example, if the data is freshly loaded). A value of 1.0 is ideal. A value of 2.0 indicates that there are twice as many fragments than there would be when the data is freshly loaded.

Use the Index value to determine whether to dump and reload your data to improve fragmentation. If the value is 2.0 or greater, dumping and reloading will improve performance. If the value is less than 1.5, dumping and reloading is not warranted.

Scatter Factor

Degree of distance between records in the table.

Scatter Factor in the dbanalys or tabanalys shows the degree of distance between records in the table. It is a good indicator of the quality of records fragmentation. The ideal value is '1', indicates that all records from one table occupy completely contiguous database blocks.

A high scatter factor index will result a low buffer hit rate. Therefore the busier the disk drives and the worse the performance.

If the value is 1.5 or greater, performance will be poor for sequential record access, and dumping and loading the data might be warranted. A value of 1.5 or greater might also reduce performance for random access.

In above report output, the 'Scatter Factor' reached 3.6, means records are scattered 36 times wider than the ideal.

Best Practice

The best achievable Scatter Factor Index value comes from a freshly loaded database with no simultaneous load running and only one table per area and indexes in separate area.

Use Type II Storage Areas with either a single tables or multiple tables for lower scatter factor.

After dumping and loading the database, baseline the scatter factor which will assist in future decision making (eg: when it's time to dump and load again).