September 21, 2007
We've looked at several metrics that can help you discover the presence of physical design "opportunities for improvement", or problems. But, as many of you know, I don't like the word "problems" - 1) Problems is too negative sounding, and 2) Sometimes inefficiencies, or tuning opportunities, aren't severe enough to merit the label "problem". "Status Quo" operations for many databases may include many inefficiencies but the performance delivered is "good enough" - well, that is, it's "good enough" until the next hardware upgrade cost comes due or the application falls over and dies when 20 more users are added.
The Index Read Efficiency (IREF) metric, the Synchronous Read Percentage (SRP), and Bufferpool Logical Index Reads per Transaction (BPLITX) can all tip us off to the presence of severe problems or physical design inefficiencies.
Sometimes this occurs when single (individual) columns of a table are indexed, and the WHERE predicates reference a number of individually indexed columns. DB2's optimizer may use the multiple-index access method. Qualifying RIDs (Row IDs) are found from the first index, then qualifying RIDs are found from a second index, then DB2 puts the RIDs into a blender to find the intersection of the qualifying RIDs, and then these RIDs are used to retrieve the data from data pages.
It can be much faster and much more efficient to use composite (multi-column) indexes which provide DB2 with a precise path to the qualifying RIDs via a single index. If a single index can be used, the whole business of accessing multiple indexes and putting RIDs into the blender (sorting the RIDs and discovering intersecting values) can be avoided.
In fact, in some recent tests I've done in preparation for IDUG 2008 in Dallas, using well designed composite indexes can cut query response times and CPU utilization by 50% or more, while, at the same time, improving LOAD, INSERT, UPDATE, and DELETE performance. Yes, it is true that using composite indexes may require additional disk space - but, no worries - "Disk is cheap" and, even if it isn't, DB2 9 provides deep compression.
The magic and wonder of all this is rather hard to describe with plain text words in a blog post. Which leads me to...
The Shameless Marketing Moment
On Thursday October 11th at 10:00am CST, and on future occasions to be scheduled, DBI is presenting a Webinar which includes DB2 LUW Physical Design Techniques (free education). You will learn about some Index Design best practices with deep dives, graphs, charts, and performance science projects on some of the index techniques. You can register by simply visiting DBI's home page for our current Webinar offering: www.Database-Brothers.com
Just for Fun
I'm going to use this section of the blog to make an apology and plug a good product. I intended to blog weekly, and I'm sorry I've been away since September 4th. I recently had a health opportunity for improvement (a very bad cold complete with coughs and all that junk) which has set me back a bit. Fortunately, on my pharmacist's recommendation, I have discovered Mucinex-D. This cold medicine really helps clear up a congested head. In the US, a prescription isn't required, but you may have to ask your pharmacist for it - they keep it behind the counter. So, if you didn't know about it, now you know to ask your pharmacist for Mucinex-D. And now my disclaimer - I'm not a doctor of medicine, just databases [grin]. Be sure to ask your doctor or pharmacist if Mucinex-D is right for you.
Stay Healthy,
Until next time,
Scott
Scott Hayes
President & CEO, DBI
www.database-performance.info
www.database-auditing.info
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2007/09/db2_luw_perform_10.html
« DB2 9 Fundamentals: Sample certification questions | Main | DB2 LUW Performance: Catalog Cache »
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.