Get access plan via Explain Plan

Build the PLAN_TABLE in the current schema by runnning this in SQL*Plus:

--in case there is already an old version
drop table plan_table;
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

The you can use the dbms_xplan package to get a nicely formatted display of the plan (use @$ORACLE_HOME/rdbms/admin/dbmsutil.sql to create if it doesn't exsit).

select * from table(dbms_xplan.DISPLAY());

to get the plan, e.g.

explain plan for
select table_name from user_tables where table_name like '%E';
select * from table(dbms_xplan.DISPLAY());

will give you something like

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

-------------------------------------------------------------------------------------
| Id  | Operation                         |  Name           | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |       |       |       |
|   1 |  NESTED LOOPS                     |                 |       |       |       |
|   2 |   NESTED LOOPS OUTER              |                 |       |       |       |
|   3 |    NESTED LOOPS OUTER             |                 |       |       |       |
|   4 |     NESTED LOOPS OUTER            |                 |       |       |       |
|   5 |      NESTED LOOPS OUTER           |                 |       |       |       |
|   6 |       NESTED LOOPS                |                 |       |       |       |
|   7 |        TABLE ACCESS BY INDEX ROWID| OBJ$            |       |       |       |
|*  8 |         INDEX RANGE SCAN          | I_OBJ2          |       |       |       |
|*  9 |        TABLE ACCESS CLUSTER       | TAB$            |       |       |       |
|* 10 |         INDEX UNIQUE SCAN         | I_OBJ#          |       |       |       |
|  11 |       TABLE ACCESS BY INDEX ROWID | OBJ$            |       |       |       |
|* 12 |        INDEX UNIQUE SCAN          | I_OBJ1          |       |       |       |
|* 13 |      INDEX UNIQUE SCAN            | I_OBJ1          |       |       |       |
|  14 |     TABLE ACCESS CLUSTER          | USER$           |       |       |       |
|* 15 |      INDEX UNIQUE SCAN            | I_USER#         |       |       |       |
|  16 |    TABLE ACCESS CLUSTER           | SEG$            |       |       |       |
|* 17 |     INDEX UNIQUE SCAN             | I_FILE#_BLOCK#  |       |       |       |
|  18 |   TABLE ACCESS CLUSTER            | TS$             |       |       |       |
|* 19 |    INDEX UNIQUE SCAN              | I_TS#           |       |       |       |
-------------------------------------------------------------------------------------

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

   8 - access("O"."OWNER#"=:B1)
       filter("O"."NAME" LIKE '%E')
   9 - filter(BITAND("T"."PROPERTY",1)=0)
  10 - access("O"."OBJ#"="T"."OBJ#")
  12 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  13 - access("T"."BOBJ#"="CO"."OBJ#"(+))
  15 - access("CX"."OWNER#"="CU"."USER#"(+))
  17 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
              "T"."BLOCK#"="S"."BLOCK#"(+))
  19 - access("T"."TS#"="TS"."TS#")

Note: rule based optimization

You can only get a cost based plan from the optimizer if you have collected statistics for the tables and indexes in this query. The rows/bytes/cost columns in the table above will then also contain data.