You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




20 January 2006

difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);.

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

SQL> set autot trace exp
SQL> delete emp;

14 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> set autot off
SQL> select count(*) from emp;
0
SQL> roll
Rollback complete.


but explain plan does not
SQL> select count(*) from emp;
14

SQL> explain plan for delete emp;

Explained.

SQL> select * from table (dbms_xplan.display);
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
14

3 Comments:

Blogger William Robertson said...

You don't need to type "EXPLAIN PLAN FOR..." if you have a handy script ;)

http://www.williamrobertson.net/code/xplan.sql

20/1/06 13:14  
Blogger Laurent Schneider said...

> 0 EXPLAIN PLAN SET STATEMENT_ID = '&STATEMENT_ID' FOR

Lifesaving trick to insert something before line 1!

20/1/06 14:31  
Anonymous Gabe said...

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

Well, autotrace has nothing to do with the rows ... the rows got deleted bacause of the "delete" statement itself.

26/1/06 17:47  

Post a Comment

<< Home