Formatted SQL Execution Plan

To see Explain Plan result in the formatted way, oracle provided a PL/SQL package called "dbms_xplan". Below is the example, which explain how to use this package.

SQL> DELETE FROM plan_table WHERE statement_id = 'SACH';

4 rows deleted.

SQL>
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'SACH'
3 FOR
4 select * from scott.emp where deptno = '10';

Explained.

SQL> /

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.DISPLAY('PLAN_TABLE','SACH'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | EMP         |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("EMP"."DEPTNO"=10)

Note: rule based optimization

14 rows selected.