How To Get Execution Path Reports on Query Statements? - Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool

If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial exercise bellow shows you a good example:

SQL> CONNECT HR/retneciyf

SQL> SET AUTOTRACE ON EXPLAIN

SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
  2  FROM EMPLOYEES E, JOBS J
  3  WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

LAST_NAME             SALARY JOB_TITLE
----------------- ---------- -----------------------------
King                   24000 President
Kochhar                17000 Administration Vice President
De Haan                17000 Administration Vice President
Russell                14000 Sales Manager
Partners               13500 Sales Manager
Hartstein              13000 Marketing Manager

6 rows selected.

Execution Plan
-----------------------------------------------------------
Plan hash value: 3851899397

-----------------------------------------------------------
Id|Operation           |Name     |Rows|Bytes| Cost|Time   
  |                    |         |    |     |/%CPU|
-----------------------------------------------------------
 0|SELECT STATEMENT    |         |  59| 2832|  4/0|00:00:01
 1| NESTED LOOPS       |         |  59| 2832|  4/0|00:00:01
*2|  TABLE ACCESS FULL |EMPLOYEES|  59| 1239|  3/0|00:00:01
 3|  TABLE ACCESS      |JOBS     |   1|   27|  1/0|00:00:01
  |     BY INDEX ROWID |         |    |     |     |
*4|   INDEX UNIQUE SCAN|JOB_ID_PK|   1|     |  0/0|00:00:01
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SALARY">12000)
   4 - access("E"."JOB_ID"="J"."JOB_ID")