SQL Tuning by Formatting

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.

8 Responses to SQL Tuning by Formatting

  1. Jeff Kemp says:

    And I thought I was alone in this. I’ll do this with complex PL/SQL code as well – although only the bits that I need to get a handle on.

    It gets to the point that I can tell at a glance which portions of a package I’ve examined before, and which parts I haven’t yet.

  2. dombrooks says:

    Jeff – you’re not weird.

  3. chet says:

    wow…3 people do this (at least)? I’m not crazy after all.

    That’s the very first thing I do when I get a query…reformat it so that I can read and understand it better.

    Yeah, I’m not alone!

  4. jametong says:

    I will reformat the sql also.. I’m not alone.. yeah!!

  5. Centinul says:

    I do the same thing too 🙂

  6. dombrooks says:

    Of course you do, it’s a logical and reasonable first step for logical and reasonable people.
    Now, if only we could all agree how to reformat it 🙂

  7. chet says:

    I thought we did agree, commas belong at the end. :p

  8. 100 % agreed with this approach. Creating Tree diagram along with formattng gives perfect picture of query logic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: