Tuning

I have a love hate relationship with query tuning. Part of me enjoys the challenge, the solving of a puzzle, but on the other hand there is that naked fear of failing to meet a customer’s expectations.

I prefer the type of problem where I am told the business objective of the query and then left to it – sometimes I can be very creative. Occasionally, like today I am handed a query and an explain plan and told “go, fix!” Today it was a simple data warehouse query joining two fact tables and five dimensions expressed through in-lists, but the explain plan had over 1500 steps. There were parallel processes kicking in all over the place and the two fact tables were old-style partition views (this was a Oracle 7.3 DW that had been pretty much left untouched until it arrived on 10g – the customer is going to put real partitioning in soon, but for now needs to get the current code working) The query was running in around an hour compared to the expectation (based on the previous system) of five minutes. A quick look around the system show some missing stats, easy to fix, but still not enough to reach my target. Then the realisation hit – get rid of the parallel – most of the query would be spent coordinating the results of all those parallel slaves. Things look brighter already.

20 July update Brighter, yes but still not quite there - asked the customer to disable the bitmap indexes - now down to minutes and not hours. Good enough for now but one to revisit when we implement partitioning later in the year