Order by clause slows down a query
I've been trying to upgrade our database from version 10 to version 11 (11.2.0.3) to accommodate the new version of the software running here.
We have a big select command (we cannot change as it is generated by the application itself), which runs in version 10 in less than 3 seconds. When I run it in version 11, I get the time multiplied 10 times (25 to 30 secs). Then I have another query which runs even worse. It takes 5 seconds in version 10 and then it takes 45 seconds in version 11.
I fine tuned all the database parameters and then I started stripping the query to see which operation was slowing down the query. It happened to be the ORDER BY. I checked and the order by field is the primary key of one of the tables (hence there is an index there). If I run the last query I mentioned above, in version 11, with the order by the query runs in 45 seconds (give or take), but if I take the order by off, it takes only 5 seconds (the same than in version 10G with the order by