difference between explain plan and execution plan

Question

Can anyone explain me what is the difference between execution plan and explain plan.

When I execute

 set autotrace traceonly;
 select * from emp where empno=7369;

Execution Plan
----------------------------------------------------------
  0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=1 Card=1 Bytes=38)
  1    0    TABLE ACCESS BY INDEX ROWID SCOTT.EMP (Cost=1 Card=1 Bytes=38)
  2    1      INDEX UNIQUE SCAN SCOTT.PK_EMP (Cost=0 Card=1)


 Explain Plan

 explain plan for select * from emp where empno=7369;
 select * from table(dbms_xplan.display);

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

I am getting the same output, so what is the difference between the two.

score 14 · Answer 1 · answered May 13 '12 at 15:21

14

The explain plan is what the optimizer thinks will happen when you run, the execution plan is actually happened when you ran the query.

See link here.

http://tkyte.blogspot.co.uk/2007/04/when-explanation-doesn-sound-quite.html

answered May 13 '12 at 15:21

steve godfrey

1,234
7
14

score 10 · Accepted Answer · answered May 13 '12 at 14:53

10

explain plan is the statement that is used to display the execution plan.

The two samples you have shown are just formatted differently, that's all.

You did not tell us how exactly you generated those outputs nor which tool you were using.

But if'm not mistaken, one of them is the output of an autotrace inside SQL*Plus the other the output when using of of the procedures of the dbms_xplan package.

answered May 13 '12 at 14:53

:Yes i have used the above mentioned menthod to generate the explain plan and execution plan .But i don't know why people sometimes ask for explain plan and sometimes execution plan? – Gaurav Soni May 13 '12 at 15:00
@GauravSoni: that's just wording. When they ask for the "execution plan" they focus on the output. When using the term "explain plan" they focus on how to run it (usually the question will be "show us the **output** of explain plan) – May 13 '12 at 15:46
@a_horse_with_no_name can you please explain exactly how should I interpret the table at the bottom of the execution plan? – Songo May 19 '12 at 16:36
@songo: I don't understand your question. Do you mean the part where it says "*SELECT STATEMENT*"? – May 19 '12 at 17:13
@a_horse_with_no_name No I mean the whole table :) What I understand is that this table is the execution plan for the following statement `explain plan for select * from emp where empno=7369;`. The table is supposed to show the execution plan, but I don't get what's in the table itself. Mainly the 3 operations and their cost. Will the optimizer run those 3 operations in series? will it choose between `operation 1` and `operation 2`? – Songo May 19 '12 at 17:40
@songo: please read the manual. This is all documented: http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm also search on http://asktom.oracle.com – May 19 '12 at 17:45

score 1 · Answer 3 · answered Jul 17 '20 at 12:38

The EXPLAIN PLAN statement is used to display the plan operations chosen by the optimizer for a SQL statement. The first thing I want to clarify is that when you have EXPLAIN PLAN output, you have the estimated execution plan that should be used when the SQL statement is actually executed

The actual execution plan for a SQL statement is produced when a statement is executed

Source: Pro Oracle SQL

http://rsmt.it.fmi.uni-sofia.bg/OracleDB/Pro%20Oracle%20SQL,%202nd%20Edition.pdf

The difference can be significant depending on the current situation (e.g. available memory, statistics).

difference between explain plan and execution plan

3 Answers3

Linked