SQL Performance (MOSC)

MOSC Banner

Order by clause slows down a query

edited Nov 28, 2015 10:00AM in SQL Performance (MOSC) 2 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center