The execution plan can be display by using following methods

1- Using simple query:(base is PLAN_TABLE)
Display execuation plan for the the last "EXPLAIN PLAN" command. You need to format result yourself.

2- Utlxpls.sql or utlxplp.sql scripts (for serial or parllel queries) (base is PLAN_TABLE)
Displays the contents of a PLAN_TABLE. Makes it much easier to format and display execution plans.

1
2

@ORACLE_HOME\RDBMS\ADMIN\Utlxpls.sql :FOR serial quieries
@ORACLE_HOME\RDBMS\ADMIN\utlxplp.sql :FOR parallel quieries

Note: Executing individual scripts or using DBMS_XPLAN is same.

3- Using DBMS_XPLAN (As of 9i) (base is PLAN_TABLE)

DBMS_XPLAN.DISPLAY function that displays the contents of a PLAN_TABLE. Makes it much easier to format and display execution plans.

1

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DBMS_XPLAN.DISPLAY_AWR Function look up an historical SQL statement captured in Oracle 10g's Automatic Workload Repository (AWR), and display its execution plan. This gives you a seven-day rolling window of history that you can access.

4- Using V$SQL_PLAN Views (base is SQL Statement)

After the statement has executed V$SQL_PLAN views can be used to display the execution plan of a SQL statement. Its definition is similar to the PLAN_TABLE. It is the actual execution plan and not the predicted one – just like tkprof and even better than Explain Plan.

V$SQL_PLAN_STATISTICS provides actual execution statistics (output rows and time) for every operation

V$SQL_PLAN_STATISTICS_ALL combines V$SQL_PLAN and V$SQL_PLAN_STATISTICS information

Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated by default. The option statistics_level=all must be set.

5- Using Toad (base is SQL Statement) TOOLS > SGA Trace / Optimization