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. A number of cost metrics to help you assess the effectiveness and efficiency of your tuning
efforts:
- DB Bufferpool Logical Reads/TX"
- Sorts/TX"
- Hash Joins/1,000 TX"
Since some people like to monitor their databases to see how fast they are going, 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, 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.