June 08, 2007
How many rows must be read (evaluated) to retrieve one row? If DB2 lacks sufficient indexes to filter the result set according to the WHERE predicates, then DB2 will have to evaluate many, possibly too many, rows from the data pages to find result sets.
The Index Read Efficiency (IREF) Metric:
Remarkably, despite its name, the formula has nothing to do directly with indexes. Instead, we need to look at the ratio of rows read (evaluated for inclusion in the result set) versus how many rows were actually fetched.
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.
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 & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info
PS - Remember to post a comment and share your values with readers. Alternatively, if you've got a time saving idea, share that instead!
« DB2 LUW Performance: Average Result Set Size (ARSS) | Main | Cheetah is now out of the gate »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.