Organizational Research By

Surprising Reserch Topic

difference between explain plan and execution plan


difference between explain plan and execution plan  using -'oracle,query-execution-plans,explain-plan'

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.
    

asked Sep 24, 2015 by ajit.chavhan
0 votes
3 views



Related Hot Questions

2 Answers

0 votes

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 Sep 24, 2015 by kinnari
0 votes

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 Sep 24, 2015 by vickeykumar66

...