Brother-Eagle® DB2 Performance
Advice: Index Read Efficiency

Curiously, the astute reader will note that the formula for Index Read Efficiency (ROWS_READ / ROWS_SELECTED) has nothing to do with indexes.

"Rows Read" tells us how many rows DB2 picked up, took a look at, and decided if a row belongs in the result set or not. "Rows Selected" indicates how many rows were actually returned from the SQL query.

If DB2 picks up and examines one or two rows to return one row, that's a pretty good ratio. On the other hand, if DB2 picks up and examines thousands of rows to return one row, then that's a symptom of scans and inefficiency. DB2 will perform scans when it does not have the necessary indexes defined to help filter and narrow result sets to the actual qualifying rows.

Index Read Efficiency < 10

If you have an OLTP database (as indicated by an Average Result Set Size less than 10), an Index Read Efficiency ratio of less than ten is desirable. This would indicate that high quality indexes are in place to help DB2 efficiently arrive at result sets.

Index Read Efficiency >= 10

• If you have an OLTP database, an Index Read Efficiency ratio of ten or higher is cause for concern. This would indicate that indexes providing sufficient filtration quality are not available. DB2 may be performing scans or using inefficient indexes as a poor substitute.
• Regardless of database type (OLTP or Data Warehouse), Brother-Eagle will flag the Index Read Efficiency with CAUTION if this value is greater than or equal to ten. If the measured value is greater than 100, Brother-Eagle will flag the value as an alert.
• To improve the database Index Read Efficiency ratio, perform SQL Equalization and Cost Aggregation (per US Patent# 6,772,411) with Brother-Panther™ to find statements having the highest aggregate read I/O cost, then work to reduce the I/O costs of these statements by creating appropriate indexes.

Index Read Efficiency >= 100

• If you have an OLTP database, absolutely DO NOT UPGRADE YOUR HARDWARE. Instead, your company can save thousands of dollars by identifying and creating appropriate indexes that support the SQL workload. Again, DBI recommends Brother-Panther™ to achieve maximum performance and efficiency.
• If you have a Data Warehouse database, consider creating indexes, MQTs, or using MDC tables to improve I/O efficiency of the most costly database queries.

---

View full list of DB2 LUW advice topics