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.