« change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime
static expression »

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

This entry was posted on Friday, January 20th, 2006 at 10:17 and is filed under oracle, dba. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

3 Responses to “difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN”

  1. William Robertson Says:
    January 20th, 2006 at 13:14

    You don’t need to type “EXPLAIN PLAN FOR…” if you have a handy script ;)

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

  2. Laurent Schneider Says:
    January 20th, 2006 at 14:31

    > 0 EXPLAIN PLAN SET STATEMENT_ID = ‘&STATEMENT_ID’ FOR

    Lifesaving trick to insert something before line 1!

  3. Gabe Says:
    January 26th, 2006 at 17:47

    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.

Leave a Reply

Use <code> and </code> to post code