Oracle 11g: How to display explain plan

Intallation
To retrieve the execution plan for a specific sql request or session, you need to install the PLAN_TABLE table in your database.
Oracle provide the utlxplan.sql script located under ${ORACLE_HOME}/rdbms/admin/ to perform its installation.

SQL>@?/rdbms/admin/utlxplan;

Calculate plan for a specific request
To populate the PLAN_TABLE table for a specific request :

SQL>truncate table PLAN_TABLE;
SQL>explain plan for select * from user_tables;

Display plan for the previous request

SQL>set linesize 400
SQL>@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4164660786

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1831 |  4173K|   515   (1)| 00:00:07 |
|*  1 |  HASH JOIN RIGHT OUTER      |               |  1831 |  4173K|   515   (1)| 00:00:07 |
|   2 |   INDEX FAST FULL SCAN      | I_OBJ2        | 12815 |   300K|    33   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER     |               |  1831 |  4130K|   482   (2)| 00:00:06 |
|   4 |    TABLE ACCESS FULL        | USER$         |    93 |  1581 |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER    |               |  1831 |  4100K|   478   (1)| 00:00:06 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   6 |     INDEX FAST FULL SCAN    | I_OBJ1        | 12815 |   100K|    11   (0)| 00:00:01 |
|*  7 |     HASH JOIN               |               |  1831 |  4085K|   466   (1)| 00:00:06 |
|   8 |      TABLE ACCESS FULL      | TS$           |     5 |    95 |     3   (0)| 00:00:01 |
|*  9 |      HASH JOIN RIGHT OUTER  |               |  1831 |  4051K|   463   (1)| 00:00:06 |
|  10 |       TABLE ACCESS FULL     | SEG$          |  5235 |   230K|    43   (0)| 00:00:01 |
|* 11 |       HASH JOIN RIGHT OUTER |               |  1831 |  3971K|   419   (1)| 00:00:06 |
|  12 |        TABLE ACCESS FULL    | DEFERRED_STG$ |  2783 | 64009 |     7   (0)| 00:00:01 |
|* 13 |        HASH JOIN            |               |  1831 |  3930K|   412   (1)| 00:00:05 |
|* 14 |         TABLE ACCESS FULL   | TAB$          |  2729 |   261K|   367   (0)| 00:00:05 |
|  15 |         MERGE JOIN CARTESIAN|               |  1831 |  3754K|    44   (3)| 00:00:01 |
|* 16 |          HASH JOIN          |               |     1 |  2070 |     1 (100)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 17 |           FIXED TABLE FULL  | X$KSPPI       |     1 |    55 |     0   (0)| 00:00:01 |
|  18 |           FIXED TABLE FULL  | X$KSPPCV      |   100 |   196K|     0   (0)| 00:00:01 |
|  19 |          BUFFER SORT        |               |  1831 | 54930 |    44   (3)| 00:00:01 |
|* 20 |           TABLE ACCESS FULL | OBJ$          |  1831 | 54930 |    43   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - access("T"."BOBJ#"="CO"."OBJ#"(+))
   3 - access("CX"."OWNER#"="CU"."USER#"(+))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   5 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
   7 - access("T"."TS#"="TS"."TS#")
   9 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
              "T"."TS#"="S"."TS#"(+))
  11 - access("T"."OBJ#"="DS"."OBJ#"(+))
  13 - access("O"."OBJ#"="T"."OBJ#")
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
  17 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  20 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)

43 rows selected.

Remark : An other way to display a plan under sqlplus is :

SQL> set autotrace on;

To trace only the plan and associated statistics:

SQL> set autotrace traceonly explain statistics;

Note that you may need to enable the PLUSTRACE role for the user that activates the autotrace feature. ($ORACLE_HOME/sqlplus/admin/plustrce.sql)
If you have an error then give the 'select any dictionary' role to the user.

Remark : Using the dbms_xplan package:

•  To display the plan from the plan table
  

  SQL> select * from table( dbms_xplan.display);

•  To display the plan from the shared pool
  

  SQL> select * from table( dbms_xplan.display_cursor);

 

Displaying current plan with dbms_xplan

• Retrieve the sql_id associated to the current request in the v$sql view
• Display the plan like this:
  

  SQL> select * from table( dbms_xplan.display_cursor('<sql_id>', NULL));

Note that under 11g, the adaptive cursor sharing can generate several suboptimal plan for a sql request depending on statistics and histograms. The second parameter of the display_cursor procedure correspond to the child number so a specific plan in relation to the selectivity of parameters used in the request.
To known if a sql request is sensitive to bind values and adaptive cursor sharing:

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql WHERE  sql_text = '...';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf            0 Y N
9bmm6cmwa8saf            1 Y Y

2 rows selected.