After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went from 4 min per execution to rougly 2 hours per execution when ran from the application server (Jboss). And the question was active in the DB during all that time, so it wasn't just the jboss. I never mananged to explain why this happened, because when I copied it from active session, bound the variables to the data logged in the application, it ran on 4min directly in the DB. The wait events were mostly scattered and sequential reads, no weird stuff. My best guess is typecasting issues in some form resulting in a different execution plan than the one i got directly in the DB.
Then, all of a sudden, it stopped being an issue. After rougly 1,5days, it's back on it's normal execution times. The maintainance crew says they did nothing. The DBAs says they did nothing. It was rougly 12h off from the latest statistics being gathered, and the server wasn't under alot of strain at the time so shouldn't be resource bound.
Is there any factor that can make Oracle suddenly change execution plans? Have anyone seen anything like this before? The SQL contains a view - is there any way I can see when the structure of that view was last changed? Does anyone have any other tips on how to find out what really happened?