(To ShrutiM and sonaliak)

Regarding the explain plans and statistics from my previous post -

If you just want to format the output of explain plan in a more readable format, there is a bunch of various scripts all over the internet, a couple of them even in oracle documentation. However I find it somewhat clumsy to mess with "EXPLAIN_PLAN_and_then_SELECT_FROM_PLAN_TABLE" for each and every sql I want to tune. Nowadays every decent DBA and developement tool or utility incorporates graphical explain plan facility, where you even don't have to actualy run the query.

Long time ago even SQL*Plus entered the arena of those "decent tools" ;-)) and offers an excelent explain plan and query statistics. The basic syntax is

SET AUTOTRACE ON|OFF|TRACEONLY [EXPLAIN] [STATISTICS]

When you set the autotrace on then the result of every query will be followed by an explain plan and/or statistics. There are various combinations possible. I find the following particularlly useful:
SET AUTOTRACE TRACEONLY EXPLAIN
because the query you isue will not actually be run, only explained, which means you can tune long running queries without actually waiting a long time for them to finish.

A word of caution on statistics this facility offer. If you look closely on the statistics in my previous post you'll find it reported a memory sort on both queries. But if you look at the queries it is more than obvious that queries required no sort (no ORDER BY or GROUP BY or merge joins, ....). The sorts were actually performed internaly by explain plan facility when outputing the explain plan! So the statistics it reports does (probably) actually belongs to both the analyzed query and the internal queries on plan table.

If on the other hand you are not familiar with how to read and interpret the explain plan and statistics, then you should find more information in manuals and books. I myself find the Tuning Guide from official Oracle documentation very usefull.

HTH,