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

5 thoughts on “difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN”

  1. 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.

  2. DML will not affect any rows if you leave the “;” at the end of the DML.

    Example:
    ========
    – no “;” at the end of delete statement

    delete from emp

    save /tmp/plan.sql REPLACE

    set autotrace traceonly explain statistics

    @/tmp/plan

    set autotrace off

    – *** if you then decide to execute the DML statement, add the line below
    r

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>