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)
Brother-Eagle presents "Logical Index Reads/TX" as a delta measurement across user defined sampling intervals.
"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.
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:
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.