I recently migrated our system form 10g to 11g. Now when a certain detailed query runs the system hangs. The query never seems to return (or if it does it is very very long). Rebuilding indexes seems to fix this issue for a little while but then it comes back again. This query is run all the time when new documents are created in our system so it impacts and prevents anyone trying to do that. We don't have this issue on the old 10g system that uses the same query.
I'm pretty new to Oracle so any advice re: log files and scripts to try is helpful.
Thanks,
Scott