Laurent Schneider

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