The method to generate explain plan.

 Execute an explain plan command on a SELECT statement:

EXPLAIN PLAN FOR

SELECT * FROM emp E, dept D

   WHERE E.deptno = D.deptno

   AND E.ename='prabha';

Display the generated explain plan

SELECT * FROM TABLE(dbms_xplan.display)

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original

-----------

Plan hash value: 250240233

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    570|     10(60) |  00:00:01|

|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")

   2 - filter("E"."ENAME"='prabha')

21 rows selected.