Yesterday I have found a saved page on "Use EXPLAIN PLAN and TKPROF To Tune Your Applications" in friend Pavan's folder in my machine. It is really good paper on Explain Plan and Tkprof. I saw many pages on these topics but, no one covered how to use them, but they just used it. This paper clearly explained how to interpret Explain Plan and Tkprof output. Here you go, make use of it...

Use EXPLAIN PLAN and TKPROF To Tune Your Applications
http://www.dbspecialists.com/presentations/use_explain.html

Examples that Roger Schrag (author) used for Explain Plan are good and well enough to interpret the Explain plan output. Here I am putting (pasting) few good points that he covered in that paper. But, I would suggest to read the paper (I have got 25 pages with typical ms word settings).

• A nested loops join operation always takes two inputs: For every row coming from the first input, the second input is executed once to find matching rows. A hash join operation also takes two inputs: The second input is read completely once and used to build a hash. For each row coming from the first input, one probe is performed against this hash. Sorting operations, meanwhile, take in one input. When the entire input has been read, the rows are sorted and output in the desired order. The merge join operation always takes two inputs, with the prerequisite that each input has already been sorted on the join column or columns. The merge join operation reads both inputs in their entirety at one time and outputs the results of the join. Merge joins and hash joins are usually more efficient than nested loops joins when remote tables are involved, because these types of joins will almost always involve fewer network roundtrips.

• Oracle is able to perform simple filtering operations while performing a full table scan. Therefore, a separate filter operation will not appear in the execution plan when Oracle performs a full table scan and throws out rows that don’t satisfy a WHERE clause. Filter operations with one input commonly appear in queries with view operations or HAVING clauses, while filter operations with multiple inputs will appear in queries with EXISTS clauses.

• An important note about execution plans and subqueries: When a SQL statement involves subqueries, Oracle tries to merge the subquery into the main statement by using a join. If this is not feasible and the subquery does not have any dependencies or references to the main query, then Oracle will treat the subquery as a completely separate statement from the standpoint of developing an execution plan—almost as if two separate SQL statements were sent to the database server. When you generate an execution plan for a statement that includes a fully autonomous subquery, the execution plan may not include the operations for the subquery. In this situation, you need to generate an execution plan for the subquery separately.

- Karteek