DB2 Performance Advice: Logical Reads/TX

One of the best cost metrics for measuring tuning efficiency and effectiveness is the "(Database Buffer) Pool Logical Reads per Transaction."

The formula is:
BPLRTX = (POOL_DATA_L_READS + POOL_INDEX_L_READS) / ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS)

• Down is good. When DB2 has to do fewer logical read I/Os within the bufferpool to complete its transactions, this represents a cost reduction improvement and greater CPU efficiency.
  • Costs will go down when new indexes and other physical design techniques are introduced which help DB2 reduce I/O.
  • Costs can also go down if the number of SQL statements executed per transaction are reduced.

• Up is bad. When DB2 has to do more logical read I/Os within the bufferpool to complete its transactions, this represents performance degradation and higher CPU cost. If the cost becomes too high, you may be tempted to upgrade your hardware. Do not upgrade your hardware. Higher values tend to indicate that indexes are missing or are ineffectively designed.

After implementing a new index or other physical design change, monitor "Pool Logical Reads/TX" to verify that average logical read I/O cost is indeed going down. If the cost metric goes up, it may be appropriate to back out a recent physical design change.

---