Obtaining an Execution Plan

Whether you are a developer or a DBA it’s pretty much inevitable that you will have to look at query execution plans. Analysing even a moderate execution plan can be a complex and time consuming task but it would seem that the first hurdle that too many people fail on is simply obtaining the plan. Okay, maybe I’m exaggerating a little but far too frequently I see queries that clearly haven’t had their plan checked so maybe developers do know how to generate a plan but simply don’t bother…

In any case, this post takes a quick look at how to use the explain plan facilities built into some common tools: Quest TOAD, Oracle SQL Developer and SQL Plus.

The SQL example used below is a simple query against the classic DEPT and EMP tables that are found in the SCOTT schema:

SELECT d.deptno
,      d.dname
,      e.ename
,      e.job
FROM   dept d
,      emp e         
WHERE  d.deptno = e.deptno
AND    d.dname  = 'SALES'
ORDER  BY
       e.ename

Quest TOAD for Oracle

Quest TOAD for Oracle is a common tool for both developers and DBAs. I think I have only worked at one place where it wasn’t the defacto tool for database development. Running explain plan in TOAD is a matter of entering the query in the editor and clicking the ambulance button on the tool bar or using the hot-key of Ctrl-E.

TOAD Explain Plan button

TOAD will display the query execution plan in the Explain Plan window, which will normally be underneath the query window.

TOAD Explain Plan display

For the OLTP systems developer, TOAD will highlight in red any table scans it finds in the plan. This may or may not be a problem so don’t simply assume that anything in red is bad. However, it would be worth a closer examination to ensure it is an appropriate operation.

TOADs default display of the execution plan will show the operations being performed and the objects being access all neatly indented so as to illustrate the dependency order. However, the default display is lacking critical pieces of information: the access and filter predicates. In order to enable the display of these other details, right-click on the execution plan window and select ‘Adjust Content’ from the context menu. Scroll to the bottom of the resulting pop-up window and check the boxes next to ‘Access Predicates’ and ‘Filter Predicates’ as shown below then click the OK button.

TOAD Explain Plan options

Now when you generate an execution plan TOAD will display the access and filter predicates:

TOAD Explain Plan display

Oracle SQL Developer

Explain plan within Oracle SQL Developer is a matter of pressing the F10 key in a worksheet window containing the query to explain, as will right-clicking on the worksheet and choosing Explain Plan from the context menu. Alternately, the fourth button from the left on the worksheet button bar will do the trick too:

SQL Developer explain plan toolbar

SQL Developer will display an Explain Plan window below the editor window, as shown below.

SQL Developer explain plan display

Similar to TOAD, the query plan hierarchy is clearly visible in the display. Likewise, the child steps of any given level can easily be hidden, which is particularly useful for interpreting large plans. Right-clicking on the plan within SQL Developer displays a context menu that contains an option to export the plan to HTML.

The default display for SQL Developer includes the access and filter predicates as well as the cost for each line but not the cardinality. To display the cardinality for each operation, select Preferences from the Tools menu. In the Preferences window, expand the Database selection on the left-hand listing and choose the Autotrace/Explain Plan option. In the right-hand pane, under the Explain Plan options, check the Cardinality option as shown below.

SQL Developer explain plan options

Once checked, generating an explain plan will display an extra column containing the cardinality.

SQL Developer explain plan cardiality

SQL Plus

Generating an explain plan in SQL Plus involves running the EXPLAIN PLAN command on the query in question and then selecting the results from dbms_xplan.display, as shown below:

EXPLAIN PLAN FOR
SELECT d.deptno
,      d.dname
,      e.ename
,      e.job
FROM   dept d
,      emp e
WHERE  d.deptno = e.deptno
AND    d.dname  = 'SALES'
ORDER  BY
       e.ename
/

SELECT *
FROM   TABLE(dbms_xplan.display())
/

which gives the following output:

SQL> EXPLAIN PLAN FOR
  2  SELECT d.deptno
  3  ,      d.dname
  4  ,      e.ename
  5  ,      e.job
  6  FROM   dept d
  7  ,      emp e
  8  WHERE  d.deptno = e.deptno
  9  AND    d.dname  = 'SALES'
 10  ORDER  BY
 11         e.ename
 12  /

Explained.

SQL> SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3357797783

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |   150 |     6  (34)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     5 |   150 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     5 |   150 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPT |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   238 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("D"."DNAME"='SALES')

17 rows selected.

Perhaps not quite a pretty as the GUI tools but it’s quite understandable. The PLAN_HASH_VALUE is displayed near the top, which can be used to obtain details from various data dictionary views, including v$sql_plan.

Within the table of plan details, the asterix (*) next to a line denotes that an entry exists in the subsequent Predicate Information section, which is where the specific access and filter predicates are listed.

The call to dbms_xplan.display can be customised using the format parameter to include or suppress details. For a full list of the details that can be shown see the documentation on dbms_xplan. As an example, the following removes the bytes column from the display:

       
SELECT *
FROM   TABLE(dbms_xplan.display(format=>'-BYTES'))
/

Note, the use of named parameters in SQL is a feature of Oracle 11. In earlier versions parameters to functions within SQL can only be passed by position.

In all of the above it needs to be remembered that the plan displayed is Oracle’s best guess of what the plan will be when the query is actually executed. Generally speaking this will be accurate but certain features will cause Oracle to execute the query differently. I have previously noted bind variable peeking as a common cause for why the actual execution plan may differ from the plan described via the explan plan facility. To obtain the execution plan that was actually used when a query was executed you should use the dbms_xplan.display_cursor function, as described in the basic process monitoring post.

In parting, running explain plan over a statement whilst in development is quite trivial and should be done for virtually every statement, even if it simply confirming an obvious plan.

Advertisement