Brother-Eagle® DB2 Performance
Advice: Write Time(ms)

This metric shows the average write time duration as computed by ((POOL_WRITE_TIME ms) / (POOL_DATA_WRITES + POOL_INDEX_WRITES)). Brother-Eagle presents this metric as a delta between collection intervals so that you can determine if average write times are improving or degrading.

Average Write Time can be improved by:

1. Eliminating low cardinality, redundant, and unused indexes which cause unnecessary write I/O to maintain them.
2. Ensuring that tablespace containers have equal sizes (DMS) and are distributed across available disk drives. The last thing you want is multiple containers on the same disk, or containers placed on OS paging devices.
3. Improving the percentage of write I/O that DB2 performs asynchronously: Consider increasing the NUM_IO_CLEANERS by increments of 1, but not higher than the number of CPUs on the machine minus 1. You might also consider reducing the CHNGPGS_THRESH database configuration parameter in 5% decrements, but do not set this lower than 30% (the default value is 60%).

To reduce average write times:

• Analyze the SQL workload to find statements with the highest rows written and highest CPU costs, then implement indexes, Clustering indexes, MDC tables, or MQTs to improve statement speed and efficiency. Update, Insert, or Delete statements with High CPU cost, carefully evaluate the indexes placed on the table to ensure there are no low cardinality indexes, skewed distribution indexes, or redundant indexes. For best results in the least amount of time, DBI suggests using Brother-Panther™ for DB2 LUW.
• Find the tablespace with the slowest average Write Time(ms) and carefully analyze the definition of its containers. Why is this tablespace so much slower than the database overall? Refer to improvement advice #2 above.

Brother-Eagle will indicate a warning if the average Write Time(ms) reaches 10ms and an alert status if the average Write Time exceeds 20ms.

View full list of DB2 LUW advice topics