Home > Oracle, SQL > Show Me a Picture!

Show Me a Picture!

It’s a fact. A comic book is easier to read than a novel. A graph is easier to read than a table. One picture is worth ten thousand words. Oracle query execution plans can be very hard to read when the number of tables involved is more than a few. Query execution plans are explained in Chapter 17 of my book. A sample query execution plan is shown below; the way to read it is (page 399): Perform operations in the order in which they are listed except that if the operations listed after a certain operation are more deeply indented in the listing, then perform those operations first (in the order in which those operations are listed).

Tabular Query Execution Plan

Tabular Query Execution Plan

If you’re like me, you’ll have a really hard time figuring out the sequence in which the operations listed in the above table are performed. But if you concentrate really hard, you’ll eventually figure out that the operations are performed in the following sequence: 5, 7, 12, 14, 13, 11, 15, 10, 9, 16, 8, 6, 4, 3, 2, and 1.

What we really need is a pictorial version of the above table with the operations properly numbered. You’ll immediately agree that the following version is much easier to read.

Graphical Query Execution Plan

Graphical Query Execution Plan

The above picture was produced using the Graphviz tool. You can find instructions for producing graphical query execution plans in the article that I wrote for the journal of the Northern California Oracle Users Group (NoCOUG). You can also download the PL/SQL package and the SQL query mentioned in the article. I got this idea from a comment left on Jonathan Lewis’ blog. The SQL query generates Graphviz commands that produce a graphical execution plan for a specified SQL ID and child number. Each node in the graph is labeled with the execution statistics for the last execution of the query.

Categories: Oracle, SQL
  1. Joe
    May 28, 2009 at 3:57 pm

    Hey Iggy,

    can you make this work for TOAD_PLAN_TABLE?


  2. Nicholas
    June 24, 2010 at 10:41 am

    Thanks for taking the time to post this. Is there any chance of getting some instructions on how to use it? The code might make it obvious to Oracle folks, but I’m just a web developer (not a dba) who’s mostly familiar with MySQL and SQL Server. I found this entry while searching Google for a way to get exec plans for Oracle that look like those in SSMS. Some help on how to use/call this would be great. Thanks.

  3. Sachin
    September 7, 2010 at 12:08 am

    hello iggy,
    this is working like a charm … thanks tons ..

  4. Iggy Fernandez
    November 27, 2010 at 6:42 pm

    I’ve written a simpler version of the SQL query to generate Graphviz commands. It’s available at https://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/.

  1. No trackbacks yet.

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: