Friday, December 13, 2013

Difference between explain plan and autotrace

The main difference between explain plan and autotrace is only that expalin plan produces execution plan without execute the statement where as autrace gave execution plan after execution of the statement.

EXPLAIN PLAN

SQL> select count(*) from emp;
  COUNT(*)
----------
        14

SQL> explain plan for delete from emp where ename = 'SMITH';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 161811703
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    19 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
  COUNT(*)
----------
        14 
 


(OUTPUT SPACING HAS BEEN MODIFIED , dont try to match it)

AUTOTRACE

SQL> select count(*) from emp;
  COUNT(*)
----------
        14
SQL> set autotrace on
SQL> delete from emp where ename = 'SMITH';
1 row deleted.

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Ti
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    19 |     3   (0)| 00
|   1 |  DELETE            | EMP  |       |       |            |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    19 |     3   (0)| 00
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          7  db block gets
          7  consistent gets
          2  physical reads
       1152  redo size
        673  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

SQL> select count(*) from emp;
  COUNT(*)
----------
        13


Now You can see the difference that in case of explain plan the row count of table remain same ,
but in case of autotrace one row has been deleted and then it produce the execution plan.

2 comments:

web stats