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
(OUTPUT SPACING HAS BEEN MODIFIED , dont try to match it)
AUTOTRACE
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.
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.
Perfect explanation..
ReplyDeleteDifference between explain plan and execution plan??
ReplyDelete