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
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
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.
Hey Iggy,
can you make this work for TOAD_PLAN_TABLE?
Thanks!
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.
hello iggy,
this is working like a charm … thanks tons ..
I’ve written a simpler version of the SQL query to generate Graphviz commands. It’s available at http://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/.