yes actually developer was using that to_date. While trying to see explain plan i removed it. sorry for that.
I have asked user to change the logic .
My thinking is since he is using sub-query it might be the cause. I have asked him to make use of cursor. I have aksed him to test with bulk collect.. Do you think that will help.