How to use EXPLAIN PLAN statement and view the results using the DBMS_XPLAN.DISPLAY function

This table function simply displays the contents of the plan table.

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  = 'benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function:

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This query produces the following output:

Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 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"='benoit')

15 rows selected.

You can also use the SET STATEMENT_ID directive to identify a specific plan:

EXPLAIN PLAN SET STATEMENT_ID='EXPL1' FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','EXPL1','serial'));

If no statement_id is specified, the function will show you the plan of the most recent explained statement.

Source: Oracle Documentation