Using Oracle DBMS_XPLAN to display PLAN TABLE
Usage of this package simplifies displaying Plan Table results. Incidently, PLAN TABLE is an Oracle table (log table) that is used to log SQL TRACE information. Specifically, information related to the execution plan of SQL statement. It is an excellent tool used for tuning SQL statement.
Please make sure that the PLAN_TABLE exists in your schema.
If it does not run the following SQL Script
$ORACLE_HOME/rdbms/admin/utl_xplan.sql
To verify that the table exists
SQL>connect your_username/your_passwd
SQL>DESC PLAN_TABLE
-- here you must see the description of the plan table
Then you can start tracing the execution plan of your statement, for example
SQL>EXPLAIN PLAN FOR SELECT * FROM DEPT WHERE DEPTNO=10;
Explained
SQL>SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 1 | 21 | 1
(0)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 21
| 1 (0)|
|* 2 | INDEX UNIQUE SCAN pK_DEPT|
1 | | 0
(0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("DEPTNO"=10)
Now we know that the statement fetched one row from The dept table using the
index of the primary key PK_DEPT