Oracle EXPLAIN PLAN

The EXPLAIN PLAN command is used to capture the steps that your Oracle database will use to retrieve the data for a query. The command is used to reveal the choices made by the query optimizer – what order the retrieval steps will be taken in and what type of access is used for each step.
The EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations that Oracle performs to execute the statement. By examining the execution plan, you can see exactly how Oracle executes your SQL statement.
EXPLAIN PLAN results alone cannot tell you which statements will perform well, and which badly. For example, just because EXPLAIN PLAN indicates that a statement will use an index does not mean that the statement will run quickly. The index might be very inefficient! Use EXPLAIN PLAN to determine the access plan and to test modifications to improve the performance.
It is not necessarily useful to subjectively evaluate the plan for a statement, and decide to tune it based only on the execution plan. Instead, you should examine the statement's actual resource consumption. For best results, use the Oracle Trace or SQL trace facility and TKPROF to examine performance information on individual SQL statements.

Attention: EXPLAIN PLAN tells you the execution plan the optimizer would choose if it were to produce an execution plan for a SQL statement at the current time, with the current set of initialization and session parameters. However, this plan is not necessarily the same as the plan that was used at the time the given statement was actually executed. The optimizer bases its analysis on many pieces of data-some of which may have changed. Furthermore, because the behavior of the optimizer is likely to evolve between releases of the Oracle Server, output from the EXPLAIN PLAN command will also evolve. Changes to both the optimizer and EXPLAIN PLAN output will be documented as they arise.
The row source count values appearing in EXPLAIN PLAN output identify the number of rows that have been processed by each step in the plan. This can help you to identify where the inefficiency in the query lies (that is, the row source with an access plan that is performing inefficient operations).

You can use the explain plan command to generate the execution path for a query without first running the query. To use the explain plan command, you must first create a PLAN_TABLE table in your schema. To determine the execution path of a query, prefix the query with the following SQL:

explain plan
for

explain plan
for
select /*+ USE_HASH (bookshelf) */
BOOKSHELF_AUTHOR.AuthorName
from BOOKSHELF, BOOKSHELF_AUTHOR
where BOOKSHELF.Title = BOOKSHELF_AUTHOR.Title;

select * from table(DBMS_XPLAN.Display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1924 | 4 (25)| 00:00:01 |
|* 1| HASH JOIN | | 37 | 1924 | 4 (25)| 00:00:01 |
| 2 | INDEX FULL SCAN | SYS_C004834 | 32 | 608 | 1 (100)| 00:00:01 |
| 3 | TABLE ACCESS FULL| BOOKSHELF_AUTHOR | 37 | 1258 | 4 (25)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BOOKSHELF"."TITLE"="BOOKSHELF_AUTHOR"."TITLE")

The output includes the Cost column, which displays the relative cost of each step and its child steps. It also includes the ID values, indenting to show the relationships between steps, and an additional section listing the limiting conditions applied at each step. When tuning a query, you should watch for steps that scan many rows but only return a small number of rows. For example, you should avoid performing a full table scan on a multimillion row table in order to return three rows. You can use the explain plan output to identify those steps that deal with the greatest number of rows.