Effective DB2 Application Tuning: Team-Based and Face-to-Face

I recently spent an enjoyable few days engaged in a DB2 application performance tuning effort. The work was fun because it was successful (we reduced the run time for some complex transactions by more than 90%), and it was successful largely because it involved a cross-functional team working in face-to-face mode. We had business experts (who knew about the functionality required of the application), application developers, data experts (very knowledgeable with regard to the logical design of the underlying database), and DB2 specialists. Everyone contributed to the successful outcome.

DB2 technical expertise is certainly helpful when it comes to improving the performance of a DB2 application, but alone it may not be sufficient to achieve really dramatic results. Enlarging a DB2 buffer pool configuration might help by reducing I/O wait time. Reclustering a table might lead to better locality of reference for some SQL statements (and, therefore, fewer associated DB2 page read requests). Adding an index to a table could provide a more efficient data access path for certain queries. Increasing the percentage of free space in index pages (when the key is not continuously-ascending) could reduce leaf page split activity and help to preserve fast index scan access. All good, certainly, but big-time gains are often achieved at the statement level (as opposed to the database- or system-level), and this is where you really want to be able to leverage the complimentary skills that an interdisciplinary team can bring to the table.

Let me give you an example of what I'm talking about. Cross-functional team sitting in a room, working to significantly reduce the run-time for a particular query at the heart of a complex transaction. The query has an in-list non-correlated subquery predicate (of the form AND COL1 IN (SELECT...)), and there's an index on COL1 but it's not being used. DB2 specialist in the room notes that the predicate references the inner table in a nested loop join operation, and an in-list non-correlated subquery predicate is not indexable in that situation. He goes on to point out that an in-list predicate with an actual list of values (literals or host variables, as in AND COL1 IN (:var1, :var2, :var3...)) as opposed to a subquery would be indexable in this case. Data expert in the room indicates that the result set generated by the non-correlated subquery would be pretty small, numbering in the single digits of values. Based on that information, an application developer in the room states that it would be pretty easy for the application code to build the query with an in-list with host variable values in place of the non-correlated subquery in-list (it's a dynamically constructed and executed SQL statement). We try submitting the query with that change, DB2 uses an index in resolving the (now list-of-values) in-list predicate, and response time goes way down. Success, thanks to the contributions of several individuals on a query-tuning "SWAT team" (as the organization likes to call them) who applied their specialized knowledge in a complementary way.

A similar example, from the same "SWAT" group: a different transaction is running too long, and the DB2 and data and application experts have already done what they can and are stumped as to what to do next to achieve the desired performance improvement. Business expert in the room speaks up and says that the really long-running part of the transaction is associated with functionality that in fact doesn't have to be delivered by that particular piece of the application. Unneeded functionality removed - problem solved, thanks again to the presence of someone with the right domain knowledge in the room.

Just about as important as having an application tuning team composed of people with DB2- and data- and application- and business-related expertise is having all of those people in the same room. That may sound old-fashioned in this age of videoconferencing and virtual meetings, but I have time and again been impressed by just how much more effective a group of people working to solve a problem can be when they are working around the same physical (not virtual) conference-room table. The ideas are better, and they come faster, and there can be a real snowball-rolling-down-the-hill effect, with people building on the contributions of others and adding their own contributions in a way that doesn't seem to happen when team members are distance-separated. Yes, travel costs raise expenses, but I feel that the return on that investment is typically very attractive. There have been plenty of times when I've been told that I have the choice of working with a group remotely or going to where the people are, and I always choose the latter route because I know that I'm much more productive when I can communicate in a face-to-face way with co-workers. Are organizations being "penny wise and pound foolish" when they refuse to foot the bill for getting problem solvers from different locations into the same room at the same time? If you have a really spread-out organization then you don't want to do the "hail, hail, the gang's all here" thing all the time, but given the kind of breakthrough productivity that can be realized through in-person communication, a few days here and there can go a long way with respect to achieving objectives.

When the challenge is tough, get a group of people with the right mix of specialized knowledge, and get them together in the same place. It's a simple notion that can deliver powerful results.