The sql in our database is log and complicated. There were a few report sql statements getting so much slower in these a few weeks. It only took about 5 minutes before, but now it needs 2 hours.

I checked explain plan, there are a lot full table scan, even though there are indexes on the tables for those columns.

What could be the cause for the sql being so slow? Why does the optimizer not picking up the indexes?

Thanks!