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.

DB2 for z/OS Data Warehousing: Query Performance

In my last installment, I riffed a little on DB2 for z/OS data warehousing. I did a little compare and contrast on the topic of performance management in OLTP versus business intelligence application environments, and concluded with some words about system-level performance optimization for DB2-based BI workloads. Herewith, a few thoughts on the subject of analyzing and tuning the performance of data warehouse queries.

Don't be intimidated by the sheer size of a BI-oriented SELECT statement. Complex queries of the type regularly found within a data warehouse workload can be downright scary-looking when initially examined (I was looking through one last week that featured a 27-table join operation). The biggest query can be broken down into bite-sized chunks. Keep in mind, too, that a "big" query, in terms of the number of tables referenced, is not necessarily a bad (i.e., poorly-written) query. Particularly in a star-schema database design, in which a large, central fact table is surrounded (logically) by a multitude of dimension tables (some of which might have their own logically-associated sub-tables, making a diagram of the table arrangement look like a snowflake), it is not uncommon to see a lot of tables joined in a query. Performance can still be fine, especially if the dimension tables are on the small side and only a small percentage of the fact table rows are accessed.

Get real comfortable with EXPLAIN. EXPLAIN output (the explanation of the access path chosen by DB2 for execution of a query) can be rather voluminous for a really big query, but, as just mentioned, you can get through it if you'll just be methodical and take it step-by-step. At each step along the way, ask yourself: does DB2's decision (regarding, for example, selection of an index, or use of a join method, or the order in which tables are joined) make sense to you? If not, figure out why you and DB2 disagree.

What do you know that DB2 doesn't? Suppose a query is running too slowly, and you determine that the cause is the sub-optimal access path chosen by the DB2 optimizer. If the choice of a different available path (perhaps using a certain index) seems obvious to you, and you're wondering why it isn't obvious to the optimizer, consider that you may have knowledge of the data that the optimizer doesn't have. The optimizer knows what's shown by the statistics in the DB2 catalog. How up-to-date and accurate are those statistics? This isn't just a matter of running RUNSTATS more frequently (though that's a good thing to do) -- it's also about how you run RUNSTATS. For example, have you set up RUNSTATS to gather correlation statistics for a set of columns in a table via the COLGROUP keyword (with, perhaps, some distribution statistics thrown in through a specification of FREQVAL)? Want some help in figuring out the right way to run RUNSTATS to boost the performance of your data warehouse queries? Check out IBM's new (and free) DB2 Optimization Service Center tool.

You can generally index a table more liberally in a data warehouse versus an OLTP environment. When the workload is characterized by a high volume of quick-running transactions, with many (perhaps most) updating data in the DB2 for z/OS database, I like to be pretty conservative in terms of defining indexes on tables -- this because the aim is throughput and every index on a table makes every insert and every delete operation (and updates, if index-key column values are changed) more expensive. In data warehouse systems, data updates are often accomplished en masse during nightly ETL (extract/transform/load) runs, and while these update operations have to complete within a certain time window, that can often be accomplished even with a pretty good number of indexes, on average, defined on tables in the database (in some cases, people will drop most indexes on data warehouse tables after online query hours, then update the table data, then rebuild the indexes before the start of the new query day). While I get uncomfortable if the number of indexes defined on a table in an OLTP system exceeds 4 or 5, I'm generally OK with more indexes per table -- maybe up to the vicinity of 8-10 -- when the database serves a BI purpose. More indexes are useful for BI applications because there is more uncertainty as to which columns will be referenced in the predicates of queries.

Look for opportunities to use MQTs. Materialized query tables, relatively new on the mainframe DB2 scene, have for some time enabled DB2 for Linux/UNIX/Windows users to score big performance gains in data warehouse systems. The idea's pretty simple: a query may end up materializing a result set that is subsequently used in the generation of the final result set that's returned to the user. Such materializations are par for the course when aggregate functions (e.g., AVERAGE and COUNT) are used. It can take a while to build this intermediate result set on the fly, and if it contains a lot of rows (it might not, even if it's based on an evaluation of a lot of rows), query run time may be further elongated because the intermediate result set isn't indexed in any way. Enter the MQT -- essentially, a pre-built intermediate result set that is not only already-there, but indexable, as well. The really good part? DB2 can use an MQT in executing a query, even if the query doesn't explicitly reference the MQT -- DB2 is smart enough to figure out whether or not an MQT can be used to reduce a query's run time.

Partition those big tables. In an OLTP environment, partitioning decisions are often driven by availability considerations (e.g., reduced time to recover a partition versus an entire tablespace). For DB2-based BI applications, partitioning is a performance thing. Among other things, partitioning drives query parallelization. Something important to keep in mind: with DB2 V8 and beyond, a table can be partitioned on one key and clustered on another (meaning that data within a partition can be clustered by something other than the partitioning key). What's a big table? I'd say, most anything with a million or more rows.

Make the right table clustering decisions. Speaking of clustering, this is a really big deal in a data warehouse environment, where bunches of rows are typically retrieved in the execution of a single query (versus the smaller result sets -- often just one row -- that are more the norm for OLTP applications). When you're going after a lot of rows, locality of reference (having the desired rows physically close to each other) can make quite a difference in query run-times. Also, when tables A and B are frequently joined, it's generally a VERY good idea to have the tables clustered in the same way.