How to Read an Execution Plan

Learn to follow and understand the steps and processes in your SQL statement’s execution plan to get the information you need for tuning your SQL.

By Chris Saxon

February 11, 2020

You’re ready to scream at the computer. Your SQL query is taking forever to complete. Again!

Plans: Explain and Execution There are two types of database plans: explain and execution. An explain plan is a prediction about what might happen when you run a query. This prediction can be wrong, for many reasons. To improve the performance of any code, you need to know what the query really did. So to figure out how to make SQL faster, it’s the execution plan you need. You need the exact sequence of steps the database took to run the query, including key metrics (that help the tuning process), how many rows each operation returned, the I/O needed to process it, and how long it took to execute.

You want to make it faster, but how?

The great power of SQL is its declarative nature. You tell the database the result you want, and it figures out the best way to get it.

But if the database decides how to execute a query, what do you do to make it faster?

To answer this question, you need to see how the database accessed and joined your tables. With this information, you can see if a better approach is available.

To do that, you need to get the query’s execution plan.

But what is an execution plan? How do you read it? And how does it help you make your SQL faster?

What Is an Execution Plan?

When you send a query to the database, it’s the job of the SQL optimizer to figure out how to execute it, and that process creates the query’s execution plan. The plan is the driving directions for your query, but instead of telling you which roads to take and when to turn, it lists how to access your tables and in what order. For example, join table 1 to table 2 and then join table 3.

By looking at the route the database took, you can see if it chose the fastest path or if you can make a faster one by building a shortcut.

Here is the execution plan for a two-table join:

--------------------------------------
| Id  | Operation          | Name    |
--------------------------------------
|   0 | SELECT STATEMENT   |         |
|   1 |  HASH JOIN         |         |
|   2 |   TABLE ACCESS FULL| COLOURS |
|   3 |   TABLE ACCESS FULL| BRICKS  |
--------------------------------------

Each line in the plan is a separate operation. These operations are linked via a parent/child relationship.

The plan is a tree. The SELECT statement at the top is the root, the tables are the leaves at the bottom, and in between you’ll find a whole host of possible operations.

These fall into three categories:

• Single-child operations
• Multichild operations
• Joins

As the name suggests, a single-child operation always has exactly one operation below it in the execution plan tree. Common examples of this are grouping and sorting steps.

Multichild operations, which can have one or more operations below them, are rare. The most likely one you’ll see is a UNION (ALL) operation.

And that just leaves joins, which always have exactly two children. Each of these children may themselves be joins, tables, or any other plan operation.

How to Read an Execution Plan

In a text-based execution plan, the indentation indicates the parent/child relationship. The parent of each step is the first line above it, indented to the left. An operation’s children are those below it, indented to the right, up to the next operation at the same depth.

You could also draw the plan above to look like the image in Figure 1.

Figure 1: Image of the two-table join execution plan

To follow an execution plan, the database uses a depth-first search, which starts from the top of the plan and works down the tree to the first leaf. It then walks back up the tree to the first operation with an unvisited child.

The search then repeats the process, walking down the tree to the next leaf. From there it walks back up the tree to the next operation with an unvisited child. And this repeats until the search has read all the steps in the execution plan.

So the order of operations for the execution plan for the two-table join above is

1. Start from the top (SELECT STATEMENT) and go down the tree to the first leaf. This is the TABLE ACCESS FULL of the COLOURS table.

2. Pass the rows from this table up to the first leaf’s parent, the HASH JOIN.

3. Look for the next unvisited child of step 1. This is the TABLE ACCESS FULL of the BRICKS table.

4. Pass the rows from this table up to its parent, the HASH JOIN.

5. All the children of step 1 have been accessed, so pass the rows that survive the join to the SELECT STATEMENT and back to the client.

Note that the data flows up the plan, from the leaves to the root.

Here’s another execution plan example. This one includes a four-table join:

----------------------------------------
|  Id | Operation            | Name    |
----------------------------------------
|   0 | SELECT STATEMENT     |         |
|   1 |  HASH JOIN           |         |
|   2 |   HASH JOIN          |         |
|   3 |    HASH JOIN         |         |
|   4 |     TABLE ACCESS FULL| COLOURS |
|   5 |     TABLE ACCESS FULL| TOYS    |
|   6 |    TABLE ACCESS FULL | PENS    |
|   7 |   TABLE ACCESS FULL  | BRICKS  |
----------------------------------------

Here the order of operations is

1. Again, start from the top of the plan (SELECT STATEMENT) and go down to the first leaf. This is the TABLE ACCESS FULL of the COLOURS table in execution plan step 4.

2. Pass the rows from this table up to the first leaf’s parent, which is the HASH JOIN in step 3.

3. Find the next unvisited child, which is the TABLE ACCESS FULL of the TOYS table in step 5.

4. Pass the rows to the HASH JOIN in step 3. Step 3 has no more children, so return the rows that survive the HASH JOIN in step 3 to the HASH JOIN in step 2.

5. Search for the next child of step 2. This is the TABLE ACCESS FULL of the PENS table in step 6.

6. Pass these rows to the HASH JOIN in step 2. Step 2 has no more children, so return the rows that survive the HASH JOIN in step 2 to the HASH JOIN in step 1.

7. Repeat the process until you’ve run all the operations. So the complete order for accessing the execution plan step IDs is: 4, 3, 5, 3, 2, 6, 2, 1, 7, 1, and 0.

The arrows in Figure 2 show this order, starting with the SELECT STATEMENT going to the full-table scan of the COLOURS table:

Figure 2: Execution plan step order flow for a four-table join

More-Advanced Examples

The previous execution plan examples used straightforward joins. Here’s an execution plan that includes single and multichild operations:

-----------------------------------------
| Id | Operation              | Name    |
-----------------------------------------
|  0 | SELECT STATEMENT       |         |
|  1 |  SORT ORDER BY         |         |
|  2 |   HASH GROUP BY        |         |
|  3 |    HASH JOIN           |         |
|  4 |     TABLE ACCESS FULL  | COLOURS |
|  5 |     VIEW               |         |
|  6 |      UNION-ALL         |         |
|  7 |       TABLE ACCESS FULL| BRICKS  |
|  8 |       TABLE ACCESS FULL| TOYS    |
|  9 |       TABLE ACCESS FULL| PENS    |
-----------------------------------------

At first glance, this seems more complicated, but the process for reading it is the same:

1. Travel down the plan to the first leaf. This is the TABLE ACCESS FULL of the COLOURS table in step 4.

2. Pass the rows from this table up to the first leaf’s parent, the HASH JOIN in step 3.

3. Find the next leaf, which is the TABLE ACCESS FULL of the BRICKS table in step 7.

4. Its parent is a multichild operation—UNION-ALL—so the database will next execute steps 8 and 9. (There is an optimization—concurrent execution of UNION-ALL—that means that the database can run all of these table scans at the same time in parallel queries.)

5. Pass the rows from the tables at steps 7, 8, and 9 up to the UNION-ALL in step 6. This step combines the rows into one dataset.

6. Work back up the tree to the HASH JOIN in step 3.

7. Join the rows from steps 4 and 6, passing the surviving rows up to the HASH GROUP BY in step 2.

8. Return the grouped rows to the SORT ORDER BY in step 1.

9. Finally, return the data to the client.

Figure 3 shows the path through the execution plan:

Figure 3: Step order flow for execution plan that includes single and multichild operations

The Exception That Proves the Rule

The previous examples cover key plan types and operations. But there are edge cases everywhere when it comes to the Oracle Database optimizer and execution plans.

So to finish this article, here’s an execution plan example that breaks the “first leaf first” rule:

--------------------------------------
| Id  | Operation          | Name    |
--------------------------------------
|   0 | SELECT STATEMENT   |         |
|   1 |  SORT AGGREGATE    |         |
|   2 |   TABLE ACCESS FULL| BRICKS  |
|   3 |  TABLE ACCESS FULL | COLOURS |
--------------------------------------

A quick glance at the plan should tell you something’s amiss: There’s no join operation!

So what’s going on here?

The query in this plan includes a scalar subquery in the SELECT statement:

select (
         select count(*)
         from   bricks b
         where  b.colour = c.colour
       ) brick#
from   colours c;

The database processes the SELECT clause after the FROM clause, which means that it has to read all the rows from the COLOURS table first, even though this step appears lower in the execution plan.

So the order of operations here is:

1. Go to the first leaf in the FROM clause. This is the TABLE ACCESS FULL of the COLOURS table—execution plan step 3.

2. Pass these rows from this table back up to the SELECT STATEMENT—execution plan step 0.

3. For each of these rows, run the subquery. So next is the TABLE ACCESS FULL of the BRICKS table execution plan step 2.

4. Pass the rows from this table to the SORT AGGREGATE operation in plan step 1.

5. Repeat plan steps 1 and 2 for each row from step 3. (There is an optimization—scalar subquery caching—that means that the database can execute these steps once per distinct COLOUR value from the COLOURS table).

Summary

The Oracle Database optimizer uses depth-first search to navigate a plan. Use this algorithm to see the order the optimizer uses to access and join your tables.

But how do you know if a plan is a “good” one? And what can you do to help the optimizer find a better one?

To answer the first question, you need to look at how many rows the optimizer estimated it would process at each step and how the estimates compare to the actual number. If these estimates are wrong, a better plan is likely available.

If the estimates are accurate but your query is still too slow, you need to see if there are any data structures you can create to access data more quickly.

I’ll address both of these problems in future articles, starting with the estimation problem.

In the next article, I’ll look at how the optimizer uses database statistics to get its row estimates and what to do if these guesses are inaccurate.

Next Steps

TRY Oracle Cloud Free Tier.

LEARN more about execution plans.

Illustration by Wes Rowell