Verify fragmentation efficiency by running benchmark tests that accurately model the workload on your production database server. Fragmentation is the most difficult aspect of the database server to change after tables are populated. For detailed information about monitoring tables, tblspaces, chunks, and so on, see Monitoring Table and Fragment Use
The database server administrator must monitor I/O request queues for data in table fragments. When I/O queues are unbalanced and some fragments are used more than others, the database server administrator should work with the database administrator to tune the fragmentation strategy.
To test the fragmentation strategy for balanced access, monitor the I/O activity for each table fragment. Such monitoring is easier if each table fragment is isolated in a dbspace that consists of a single chunk. The output for onstat -D and onstat -g iof displays I/O statistics by chunk and is only meaningful if you created a single fragment per chunk. Use the onstat -D output to see where and how much I/O takes place. Ideally, you should see balanced I/O across all fragments in the table.
The onstat -g ppf command displays the number of read and write calls per fragment. Although the read and write calls do not show how many disk reads and writes occurred, you can get a good idea of the I/O activity from these columns. You can determine the table for the fragment by joining the partnum column to the partnum column in the sysfragments table, and then by joining the table id column with the table id in the systables table.
To monitor I/O activity at the operating system level, use sar -d, iostat, or a similar system utility. Look for very busy drives with transfer rates lower than 500 kilobytes per second. Low transfer rates might indicate that multiple queries are accessing the drives, which causes an increase in seek time and reduces the data transfer rate. Investigate whether tables accessed at the same time are located on the same drives and separate them.
To determine which fragments are eliminated during query processing, create SET EXPLAIN output for each query and examine the query plan, as described inAnalyze Complex Query Plans.