What I basically did was I used SQL Tuning Advisor for my long query. It suggested creating and SQL Profile which I did. I checked the performance of my query after the Profile was created, did not see much difference and decided to delete the Profile. But now when I check the execution plan of the same query (for that I created and deleted the Profile) it shows Oracle ignores all indexes and performs only TABLE ACCESS FULL for all tables! Do you know why is this happening?
It happens even if I slightly modify the query. For other queries Oracle uses indexes as before.
I tried creating and dropping the Profile again, deleting the tuning task... but no difference.
EDIT: Sorry, I checked now the past execution plans and Oracle was performing full table scans before my experiments with sql profiles as well. I was wrong when I though I unintentionally changed something and did not know what. Thanks for answers.