Getting a query Explain Plan

As an Oracle newbie, or as an experienced developer, you know the scenario - someone comes along to your favorite Oracle related forum and posts something like:

Help! I have a query that was running just fine and now it has become slow - what possible reason could there be? The query is: select * from sometable where ....

Well, obviously any number of things could be causing the query to slow down, but invariably the first thing that any helpful person will ask for is the Query Execution Plan, also known as the Query Explain Plan.

What is an Execution Plan?

One of the nice things about SQL is that you can write a query of almost limitless complexity, possibly involving many tables, columns and where clauses and the database just produces a result for you. If you think about it, there are many different ways Oracle can get that result, depending on the tables involved, some examples are:

• Full table scans and hash joins
• Index range scans and nested loop joins
• Index fast full scans
• The order the tables are joined

Oracle contains a very complex piece of software called the Query Optimizer that takes a SQL query, analyzes it and then using statistics on the tables, a set of rules and sometimes what seems like a bit of magic figures out the most efficient way of accessing the data.

This analysing process is known as Parsing the query, and along with other things, it creates an Execution Plan which is basically the set of steps Oracle must use to search the data and produce the query results.

Obtaining an Execution Plan

Assuming you have a query, getting the execution plan for it is easy. Log into SQLPLUS as usual and then use the 'explain plan for' command to generate the explain plan. Using the table created in the AutoTrace section:

SQL11G> explain plan for
select object_id from test1 where rownum = 1;

Explained.

So what happened here? Well, the 'Explain Plan For' command did infact force the query to be analysed and generated an explain plan for it, even though you cannot see it. The actual execution plan went into a table called the PLANTABLE. Generally, you never need to access the PLAN TABLE directly, Oracle has a utility that will get the results out for you, called DBMSXPLAN:

SQL11G> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1774051367

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost     (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |     2       (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY        |           |       |       |                |          |
|   2 |   INDEX FAST FULL SCAN| TEST1_UK1 |     1 |     5 |     2       (0)| 00:00:01 |
-----------------------------------------------------------------------------------

The table of results shown above is the explain for the query and provides vital information about how the query will execute at runtime. So, generating the plan for any query is a two step process:

1. Explain the query using 'explain plan for'
2. Obtain the results for last explain plan for command using the query: select * from table(dbms_xplan.display());

Reading The Explain Plan

Reading some Explain Plans is easier than others. For example the one above only involves a single index, and it's pretty clear what is going on, however if the query involved many tables, it can get complex pretty quickly. As with the other tools described in this section, learning how to read an explain plan is a job for another day, but at least now you know how to generate the plan and what it's purpose is.