EXPLAIN PLAN

Display the execution plan for an SQL statement.

Syntax:

   EXPLAIN PLAN  SET STATEMENT_ID = 'text'
      FOR statement;

   EXPLAIN PLAN [SET STATEMENT_ID = 'text']
      INTO [schema.]table@dblink
         FOR statement;

If you omit the INTO TABLE_NAME clause, Oracle will fill a table named PLAN_TABLE

Example

-- Create an empty plan table (adds a table to the current schema)
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

-- Run explain plan
EXPLAIN PLAN FOR
SELECT s.col1, s.col2, h.col3
FROM huge_table h JOIN small_table s USING (demo_id);

-- Now look at the plan created
SELECT * FROM TABLE(dbms_xplan.display);

-- Delete the records when finished
DELETE from plan_table;
COMMIT;

If the query is fast enough that it can be run to completion in a reasonable amount of time,
 then just turn on the SQL*Plus AutoTrace feature. Once turned on, this feature will display an
 execution plan for every subsequent SQL statement you run.

SQL> SET AUTOTRACE ON
SQL> SELECT s.col1, s.col2, h.col3
FROM huge_table h JOIN small_table s USING (demo_id);

Explain plan results

In an explain plan output, the more indented an operation is, the earlier it is executed.
The result of the indented operation is fed to the parent (less indented) operation. In this
 way you can see the order of execution for the whole statement.

It is possible for several operations to be equally indented and have the same parent. These
indentations are calculated from the id, and parent_id columns of the plan_table.

Operations: SELECT, INSERT, UPDATE, DELETE, AND-EQUAL, CONNECT BY, CONCATENATION, COUNT,
 DOMAIN INDEX, FILTER, FIRST , ROW, FOR UPDATE, HASH JOIN, INDEX, INLIST ITERATOR, INTERSECTION,
 MERGE JOIN, MINUS, NESTED LOOPS, PARTITION,REMOTE, SEQUENCE, SORT, TABLE ACCESS, UNION, VIEW.

There are also many Options which describe each Operation in more detail - here are a few of the most common:
TABLE ACCESS (FULL) = Full table scan
INDEX (RANGE SCAN) = Read multiple values from an index
INDEX (UNIQUE SCAN) = Read one value from an index
MERGE JOIN () = Sort two tables and merge the sorted rows
SORT (JOIN) = Sort returning multiple rows
SORT (AGGREGATE) = Sort returning one row