Bloor Research on ActiveKnowledge : “Improving On Optimisers”

"Improving on Optimisers", Philip Howard, Bloor Research : "After all these years you would assume, wouldn’t you, that database optimisers were pretty good? Companies like IBM and Oracle have been costing query plans and re-writing SQL not just for years but for decades, so you would expect that they would know what they were doing.

Well, if that’s the case then how come that ActiveBase and its UK distributor Application Performance, can making a living out of selling the former’s ActiveKnowledge product, which has been expressly designed to improve optimiser performance though, to be fair to IBM, only for Oracle?

ActiveKnowledge acts like what you might describe as a SQL Firewall. That is, it intercepts all incoming SQL, does various clever things with it, and then passes it on to the database to process. What, of course, is important is the “clever things”. So, what are these?

The main “clever thing” that ActiveKnowledge does is to assume that the optimiser doesn’t know how to calculate costs properly—indeed, that an optimiser can’t, by definition, calculate costs accurately. The company’s view is that costs can only ever be estimated and that those estimates will always include inaccuracies which, in turn, will result in queries running more slowly than would otherwise be the case.

ActiveBase contends that the only way that you can accurately calculate the costs of a query are to run the query multiple times using multiple options and actually find out in practice which is the best way to run a particular query. This is exactly what the product does: determines multiple execution strategies and then benchmarks them. For obvious reasons, therefore, the product is most suitable for environments where you have many queries that are repeated on a regular basis ... In a nutshell: it is certainly worth considering if you are an Oracle user"

I've sat through an ActiveKnowledge presentation in the past, which even came with a customer testimonial which sounded pretty convincing. I've always been wary of this product though, not so much because I don't think the CBO could be improved or that it doesn't make mistakes, but because often what these companies end up selling is what's already in the database in the first place. Has anyone reading this used ActiveKnowledge, and does it do anything that you couldn't do with a properly tuned and understood database? Also, whilst I assume it's aimed at the OLTP market, has anyone tried it with a decision support application?