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.
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.
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).