Hi Akhil,
Basically explian plan is used to find out how Oracle will execute the query.It will show whether indexes will be used or not and incase if it uses the index it will show which indexes are being used or whether the query is performing full table scans or not.
In order to get an explain plan for a query u have to create first the plan table.The plan table is created by running the utlxplan.sql.Run this script and create the plan table.
After u create the plan table,run the follwing query to generate the explain plan


sql> explain plan
set statement_id='A'
for ;

explained

sql> Run the following query to get the explain plan for the
query

select lpad(' ',2*level)
||operation||' '||options||' '||object_name
Execution_path from plan_table where
statement_id='&statementid'
connect by prior id=parent_id and
statement_id='A'
start with id=1;


e.g. Explain plan for a sample query
EXECUTION_PATH
--------------------------------------------------------------------------------
NESTED LOOPS
TABLE ACCESS FULL AP_REQ_LETTER
TABLE ACCESS BY INDEX ROWID M_USER_MAST
INDEX UNIQUE SCAN M_USER_CODE_IDX


In case of any help please be free to ask me at [email protected]

Regards,
ROhit Nirkhe,Oracle DBA,OCP 8i
[email protected]