DB2 Performance Advice: Logical IX Reads/TX

A very important cost metric for measuring index health, tuning efficiency, and tuning effectiveness is the "(Database Buffer) Pool Logical Index Reads per Transaction."

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

"Logical Index Reads/TX" can be used to identify the presence of potential index leaf page scans, as well as its value as a cost metric. As a rule of thumb, if BPLITX is greater than:
   The Number of Selects per Transaction X 4 X 1.5
then it is likely leaf page scans are occuring. Leaf page scans are CPU costly, inflate Bufferpool hit ratios, slow down your SQL, and are indicative that an index is either missing or poorly designed.

• "X 4" assumes the average/typical index has 4 levels (NLEVELS=4)
• "X 1.5" introduces a fudge factor which considers the possibility of update and delete statements

Consider an index defined on columns A, B, and C. If a statement's WHERE clause specifies "WHERE C = 88" but does not specify equal predicates for columns A and B, then a leaf page scan is likely to occur as DB2 reads across all of the leaf pages attempting to find the values of 88. Actual results may vary depending on distribution statistics that are available in the catalog.

If "Logical Index Reads/TX > The Number of Selects per Transaction X 4 X 1.5", then DBI suggests:

1. Review Explain information for your SQL workload to identify SQL with MATCHCOLS = 0
2. Use pureFeat for DB2 LUW to:
   • Identify SQL with high aggregate CPU costs
   • Access and Evaluate graphical DB2 Explain information
   • Automatically obtain index advice to cure the leaf page scans
   • Implement index solutions to lower workload execution costs and accelerate your business

After implementing a new index or other physical design change, monitor "Logical IX Reads/TX" to verify that average logical index 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.

---