Index Tuning Advisors

One of the benefits I have working for Confio is that I get some exposure to multiple database platforms (Oracle, DB2, SAP Sybase, and MS SQL). And along with those multiple platforms I also get to look at a handful of tools that are commonly used for each. I have a long way to go before anyone is going to consider me a DB2 DBA, but each day I get to take small steps by learning something new about each platform. The best way to do this is to simply have a conversation with customers while I am on site visiting with them. Recently I came across something that really struck home that I wanted to share with everyone.

Some of you may be aware that Oracle has a SQL Tuning Pack, similar to Microsoft’s Database Engine Tuning Advisor. For those that have used the DETA, you are most likely aware that you do not apply the suggestions made without first doing some analysis to see if the changes have merit. (By the way, vendors *might* want to rename indexes that are suggested by the DETA, because when I look in your code and I see a bunch of indexes named dta_* I have a good idea as to how you performance tune your database and it doesn’t give me a warm fuzzy feeling, thanks anyway).

Where was I before I got sidetracked by bashing vendors? Wait, don’t I work for one now? Not one that accepts default DETA suggestions at least…

Anyway, I came across this blog post recently which made me understand that despite each platform being different in many ways we have the same struggles. In this case we struggle with people either not knowing what they are doing or knowing what they are doing and simply being sloppy to verify their testing (and yes, it is also possible that honest mistakes are made and that data will change over time).

But here is the honest truth: the good DBAs don’t trust such tools.

Most people wouldn’t pull up to Jiffy Lube and service their car themselves while being given blind direction from a mechanic, but for some reason they think it is acceptable to do that exact same thing with their database performance. They let a computer program decide how best to tune their engine and statements and then are shocked(!) to find that performance has degraded.

If the program was that good, it would be a part of the engine by default, and Oracle/Microsoft would jack up the price for their product. Until then, actual humans are needed to help make things go faster.