Methods for viewing SQL Execution Plans

Using Autotrace

SQL> set autotrace traceonly explain
SQL> select ename from emp where sal > 500;

Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------

Using DBMS_XPLAN Package

SQL> explain plan for
select ename from emp where sal > 500;
Explained.

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

PLAN_TABLE_OUTPUT
------------------------------
Plan hash value: 2872589290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------

Using HINT 10g and Above Only

SQL> alter session set STATISTICS_LEVEL=all;
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ ename from emp where sal > 500;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID 0vxgkhh65pf8k, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where sal > 500

Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 14 |00:00:00.01 |
--------------------------------------------------------------------------

Using Dynamic Performance View

SQL> alter session set STATISTICS_LEVEL=all;

SQL> select hash_value,sql_id,operation,id,starts,
cardinality as "E-Rows",last_output_rows as "A-Rows",cost
from v$sql_plan_statistics_all
where sql_id=' 0vxgkhh65pf8k';

HASH_VALUE SQL_ID OPERATION ID STARTS E-Rows A-Rows COST
---------- ------------- -------------- --- ------ ------ ------ ----
207272210 0vxgkhh65pf8k TABLE ACCESS 1 1 1 14 2

Contributed by my colleague y.b.m