SQL Tuning by Formatting
March 24, 2010 8 Comments
In a recent post on the oracle-l mailing list, Tim Gorman made a neat summary about a very simple starting approach to SQL tuning, an approach which I’ve often found invaluable despite its apparent old-fashionedness and “ludditity”:
Everyone’s method varies, but instead of using a automatic formatting program like TOAD, consider simply re-formatting and “pretty-fying” the SQL statement by hand, in an editor (I like “vi”). By the time you finish re-arranging the SQL text to format the way you like, you’ll be intimately familiar with the logic of the SQL statement and able to start making an intelligent and focused investigation for solutions. I’ve had folks who watch me do this editing accuse me of being anal-retentive and behind the times (and I have no argument with either), but when I finish editing (i.e. big SQL statements sometimes take an hour or more) and I start discussing what the SQL statement is doing versus what the developer probably intended, the jokesters usually shut up and start listening. And, at the end of the process, it is easier to begin testing different solutions as the logic of the SQL is fresh and familiar.