Alternative performance tuning options
I was recently offered the opportunity (via the IBM Champions programme) to evaluate the IBM Bob product. My particular interest was in Db2 performance tuning (and documentation, but more of that elsewhere).
My normal approach for sub-optimal queries that surface in a customer’s database is to get the SQL parsed through explain (either db2expln or db2exfmt; the latter being the more comprehensive and accurate) so that I can take a closer look at the access path. This is sometimes enough to give an insight into where the problem might lie; any Table Scans of huge tables, or massive sort activity are the usual red flags.
Getting performance improvement advice
The next stage would be to invoke db2advis. There are a range of options, but the most basic one is to supply the SQL as an input file and ask for the default recommendation of an indexing strategy. What I did here, by way of experiment, was to take a couple of sub-optimal queries; ones that I had previously examined and determined could not be easily improved, and to push them through Bob by way of comparison.
Bad results – DZ
I don’t think I can share the actual SQL as it is taken from a customer database but, for the sake of argument, let’s call it the DZ query. It has a number of issues that might raise a DBA’s hackles immediately: redundant JOINs, the use of OR … IS NULL in Date Range conditions, etc.
So, negative news first. Bob has picked up on these issues (and a couple of others) and suggests a rewrite of the SQL to be more efficient. Unfortunately, it is actually more expensive than the original. Not by a lot; the Estimated Cost has gone up from 38 timerons to 64, but with thousands of executions every day, that could add up. Also, I really wanted to see a reduction here.
What seems to have happened is that Bob has recommended the use of DISTINCT and has lost an IXSCAN on a table as potentially redundant. As a result, we seem to have lost a Nested Loop Join on two Index Scans and instead have the NLJOIN operating on a single index directly.
I would have to spend a lot of time picking that one apart and I don’t think the effort will be justified.
Good results – VRD
I have another query (call it the VRD query) and Bob has also suggested a rewrite for this and, again, the Estimated Cost has gone up, this time from a fairly significant 30,576 to 48,713. It’s replaced comma joins with INNER JOIN … ON code, which makes the query clearer although probably doesn’t help the performance. More importantly, Bob thinks he’s identified a redundant table and removed that. That would need careful validation with the customer’s business case in order to proceed and I’m unlikely to be able to pursue that.
More importantly, Bob has found a couple of new indexes that could be useful. The reason this piques my interest is that this statement has already been pushed through the db2advis utility, and the best results suggested a new index that would give a 0.016% improvement. That is not going get anyone excited and will likely not give enough performance benefit to outweigh the cost of storing and maintaining the new index.
However, Bob has suggested two alternative new indexes and an experimental deployment of them in a full-scale test environment suggests an 11% performance benefit. That’s not huge, but it is significant enough to pursue, particularly if this query is another of those that gets executed thousands of times a day.
Conclusion
This test is pretty superficial, and I’d like to extend it to more detailed workloads, but it does add another string to our bow. If db2advis comes up blank, or with minimal improvement options, it seems that Bob can find out something more. The results still need to be tested and evaluated (in that example Bob recommended two indexes but only one gives a significant advantage) but that would be true of any suggested indexing options.
Email me at mark.gillis@triton.co.uk if you have any questions or observations and please let me know if you have any similar (or better!) experiences. Thanks to Mark Ketteman (IBM Solutions Engineer) for providing the means to evaluate Bob and for reviewing my findings.