Brother-Eagle® DB2 Performance
Advice: Phys Reads/Sec

Frankly, rates are not very helpful when it comes to tuning, but they are entertaining. Your business may be busier on Monday than it is on Wednesday. Or, your business may be busier at the end of a month versus the middle of a month. Rates can vary depending upon demand for services.

When evaluating database tuning efficiency and effectiveness, cost metrics provide a much better indicator of tuning success or failure. Brother-Eagle provides a number of cost metrics to help you assess the effectiveness and efficiency of your tuning efforts:

• Database Bufferpool Logical Reads per Transaction
• Sorts per Transaction
• Hash Joins per 1,000 Transactions

Since some people like to monitor their databases to see how fast they are going, Brother-Eagle provides "Physical Reads Per Second" as a performance metric for informational purposes only. The value shown is the delta of physical read activity (POOL_DATA_P_READS + POOL_INDEX_P_READS) since the prior measurement was taken.

As an aside, Physical Reads/Second may be helpful for capacity planning or validating the effectiveness of tuning. After several iterations of tuning activities, you may observe a decrease in Physical Read I/O activity rates. However, again, any such decrease could be due to normal changes in business activity.

---

Multi-Partition Database Tip

---

If you have a multi-partition database, and if you run multiple Brother-Eagle ticker streams on each of your partitions, it may be helpful to compare Physical Read I/O rates across the database partitions. If one partition stands out as having much higher Physical Read I/O rates than the other partitions, this may indicate an opportunity for improvement. Consider:

• You may have a load balance problem across partitions due to hash keys gone awry
• You may have some tables defined on just one or two partitions, but not all partitions
• DB2 may have marked an index bad, INDEXREC is set to RESTART instead of ACCESS, and DB2 is now doing table scans instead of using the intended (bad) index

In any event, a partition with much higher physical read I/O rates than other partitions indicates a problem that must be addressed before contacting your hardware vendor.

---

View full list of DB2 LUW advice topics