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