How to get the explain plan in oracle

We can use the DBMS_XPLAN


1.
SQL> explain plan for select * from dual;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

2 .

SQL> conn manjit/manjit;
Connected.
SQL> explain plan for select * from home;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2313634949

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     3 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOME |     3 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

12 rows selected.


SQL> analyze table home compute statistics;

Table analyzed.

SQL> explain plan for select * from home;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2313634949

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     3 |    12 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOME |     3 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.