Ajeet,
I would suggest looking at some tips at

http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/

ajeet ojha wrote:
> Hello,
>
> In my database environment for many of the queires the explain plan
> cardinality defers (huge difference) with the cardinality shown by row
> source operations of tkprof.
> I have been trying to find all possible reasons -
>
> so far I came to know that 2 main reasons -
>
> 1. limitation of cbo as mentioned in the link below
>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40230704959128
>
> it also tells that solution is such cases is use dynamic_samping.
>
> 2. the other reason is poor stats on the undelying tables/indexes.
>
> please add if there are any more.
>
> also how to collect a good stats - is there any oracle recommendation
> - best practices ?
>
> are the above 2 are only reasons or there are many more - i went to
> differnt forums and it seems that there are many more.
> please share if you have come across such sitution - possible
> solutions etc.
>
> Thanks
> Ajeet

--
http://www.freelists.org/webpage/oracle-l