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

    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. Shwetal Joshi

    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

Comments are closed.