The formula is: IREF = Rows read / Rows Selected (Fetched)
This metric can be computed at the database level (Use 'dbsnap2.txt' from the preparation materials), and also for each statement from a statement event monitor.
As a rule of thumb, for OLTP databases, IREF should be less than 10. 1 would be ideal. If your value is 8, for example, this means DB2 is evaluating 8 rows of data from the data pages to find 1 row to include in the result set. Values greater than 10 are a key indicator of physical design performance problems.
For Data Warehouse databases, having an IREF greater than 10 is common and expected. Still, as you work to make tuning improvements in your data warehouse, one of your goals should be to reduce your current IREF value. The lower you can get your database's IREF, the better your physical design - which will optimize CPU and I/O efficiency, and ultimately improve query throughput.
A shameless marketing moment...
Do you have your free copy of Brother-Eagle™ yet? Brother-Eagle will compute many of the metrics we are discussing here for you, automatically, and present them in a unique stock ticker format. Update 12 September 2015: The free edition of Brother-Eagle® is no longer available. Brother-Eagle is now part of the DBI pureFeat™ Performance Management Suite for IBM DB2 LUW.
Just for fun...
As someone who is very busy these days, and who isn't, I'm always looking for ways to save time. When I was a kid, my mom folded my underwear (please don't blush too much). When I was old enough, she made me fold it and put it away. It's funny how we drag childhood habits with us into our adult lives. Well, a few months ago I discovered I could save about 15 minutes a week if I just piled my undies up in a neat pile in put them in the drawer unfolded. Remarkably, this is going to save me about 13 hours a year. Thanks mom, but not anymore.
Until next time,
Cheers,
Scott
Scott Hayes
President & Founder, DBI Software
IBM DB2 GOLD Consultant