Tool to Assist with Basic SQL Analysis

I just came back from RMOUG Training Days conference. It was my first time in Colorado (and obviously my first RMOUG training day) and it was really great (I wrote about it in another post).

During my second session (From 4 Minutes to 8 Seconds – about a real SQL tuning case I had quite a few years ago), I mentioned that one thing that I usually do when I see a query and need to analyze it, is to take a piece of paper and draw the tables and relations between them. When I later look at the execution plan and try to understand what Oracle does, it helps a lot if I know the structure of the tables. There is a big difference between queries built like a “star” (a single table in the middle, while the others are joined to it) or a “line” (each table is joined to the next one), or any other structure.

Apparently, I’m not the only one who does that. During lunch, Andy Haack approached me and said that he does the same when he analyzes SQL statements and I’m sure Andy and I are not the only ones who do that. Andy asked me if I know of any tool that can perform basic parsing on a statement and build this simple “drawing”. I don’t know about anything like that (I’m familiar with some tools that analyze the schema and foreign keys and draw the tables structure and relations, but not tools that parse SQL statements).

I know that building such a tool is very complicated. It’s not too bad for simple statements, but if you add inline views, ANSI joins as well as Oracle syntax, WITH clauses and more it can be really tough.

So here is my question to you, do you know of any tool that does something similar? Would you be willing to write something (even a basic one that can be improved later)?


8 thoughts on “Tool to Assist with Basic SQL Analysis”

  1. To start, I often use SQLHC by Carlos Sierra. It don’t need to install on the databases and give very informative report of the SQLID provided.


  2. Hi Amitzil,
    Your post are really amazing.
    if you dont mind, I request you if you could write a post about analyzing the AWR, TRACE file & EXPLAIN plan that would be great!!


Leave a Reply

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

You are commenting using your 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