see the final cost of the query.

Check the cost of the query and the plan also avoid merge cartesian and check the type of join used. check the timing and statistics
set timing on - to check the timing.
set autotrace on - to check the statistics

1. It can be used without tracing

2. It needs the plan_table table utlxplan.sql(Pls. run the oracle supplied script it will create table(plan_table)

3. Create the explain plan

SQL> explain plan for select name from employees;

Note: The v$sql_plan view can be used to gather this information and the plan_table table will hold the information

This steps are:

A) Plan steps with no children:

A step in the plan with no dependents is a leaf of the tree (steps 6 7 and 9 in the sample above).
A leaf step will be either a Table Access or an Index Scan; the Rows (or Cardinality) column tells us how many rows the scan should return. Missing information:
How many times will the step be executed? An Index Range Scan that returns 500 rows is hardly cause for alarm;
but if it is going to be
executed 2 million times then we have a problem.

Looking at a step in isolation (and this applies to branch steps as well as leaf steps) you cannot tell how many times it will be executed; you need to look at its ancestors in the tree.
Watch for:

*
INDEX RANGE SCAN. This is probably the most insidious performance hole in Oracle.
A Range Scan can return any number of rows; 1 100 100 million - the Rows column in Explain Plan often gets it wrong.

* TABLE ACCESS FULL. Full table scans (with high row counts) when you are performing low-volume transactional SQL.
Full table scans are OK for high-volume batch processes and reports.


B) Plan steps with 1 child

Plan steps with one child fall into three main classes:


* Passive Operations

Operations such as VIEW and PX SEND simply pass data through unaltered to a parent step. They may be ignored.


* Iterative Operations

INLIST ITERATOR PARTITION INLIST PARTITION ALL PARTITION ITERATOR and PX ITERATOR all execute the child step many times.

Even though we cannot tell from the plan how many times the child steps will be executed the Rows column displays the expected number of rows for all iterations not the average per iteration. For example step 8 above expects to return 36867 rows in total not per partition. Note that this is in contrast to plan steps with 2 children (see below).


* Active Operations

All other operations with a single child are active; they receive the row set from the child do something to it then pass it on to the parent.
Watch for:

*
SORT operations with high row counts. If a result set is small enough then Oracle will perform a very efficient in-memory sort.
Beyond a certain size (depending on the setup of your database and session) the sort will need to page to disk; this can double the sort time or much worse.
This means that execution times for small volumes will not scale proportionally to larger volumes.

* FILTER is an unusual step in its single-child form. Look at the Filter condition in the Predicate Information section of the plan. If the condition references any table columns from subordinate steps then the filter is applied after the child step filtering non-matching rows as they are returned. If the condition references only bind variables and constants then it is evaluated before the child step; if the expression evaluates False then the child step is not executed at all.
Step 1 in the plan above is a good example.

* PARTITION ALL and any operation containing the word ITERATOR are iterative; they execute the child step many times.
Note that the Rows column shows the total number of rows expected for all iterations; not per iteration.

* A VIEW operation is often encountered when selecting from a database view an inline view or simply when joining a large number of tables. It is a popular misconception that a VIEW operation will cause the result set to be materialised in TEMP space before proceeding with parent steps.
This is not true; the VIEW operation appears to have no effect on the plan at all.


C) Plan steps with 2 children

There are two ways to interpret steps with two children:

1. Active: Do A then do B.
2. Iterative: For each A do B.

The difference is one of the most critical aspects of performance tuning.
NESTED LOOPS FILTER and MERGE JOIN CARTESIAN are the only iterative operations (for every A do B);


all others are active (do A then do B).

Unlike the single-child iterative operations described above the Rows measure is the expected number of rows for a single iteration of step 2. In the example above Step 8 (37 867 rows) will be performed once for each row returned by Step 4 (14 rows). So instead of 37 867 rows it is really 530 138 rows!

Watch for:

*
NESTED LOOPS and FILTER operations with a large number of rows in the first child step
especially when the second child step returns more than one row or has subordinate steps;
the cost of repeating the second child step so many times can be prohibitive.
Exception: if the second child step is a unique index scan without a TABLE ACCESS
it can be very efficient in a NESTED LOOPS or FILTER operation.

* MERGE JOIN CARTESIAN has a bad reputation from the days of the Rule Based Optimizer because
it usually
signaled a programming error and was the cause of a performance problem.
Under the Cost Based Optimizer MERGE JOIN CARTESIAN is often used to join two unrelated
tables where one table will return just a single row (or no rows).
A
Cartesian join is only a problem if both row sources in the join have a large number of rows.

* HASH JOIN is especially efficient when one of the sources is small (say <10000 rows).
The smaller table should always be the first child step in the Explain Plan.
If both tables are large or if the large table is in the first child step then Oracle
will run out of Temp space to do the join and will start paging to disk.

As a very general rule of rule of thumb low-volume SQL (eg. Screen interfaces)
should use Index Scans and Nested Loops joins;
high-volume SQL (batch jobs) should use Full Scans and Hash Joins.