I have already checked all plans and this is the one i got most optimized.
Moreover I think The actual problem is with Snaposhot too old error rather than query tunning.
May be it could be bug with oracle and i need to apply the latest patch for it right now i am running oracle 9.2.0.1.
I hope this time it won'nt give me snapshot too old error.