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
You don’t need to type “EXPLAIN PLAN FOR…” if you have a handy script 😉
http://www.williamrobertson.net/code/xplan.sql
> 0 EXPLAIN PLAN SET STATEMENT_ID = ‘&STATEMENT_ID’ FOR
Lifesaving trick to insert something before line 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.
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
**** Correction ****
the DML will end up affecting rows with my solution… Guess explain plan is the way to go for DML statements 🙂