ORDER BY slow processing
HonzaOct 4 2011 — edited Oct 6 2011Hello,
so I've found that several queries that previously ran fine (by previously I mean in the long term, several years even) now have problems processing and take a long time because of sorting. For example a query will run under 10s, but once I add an ORDER BY statement, it will spike to a minute or even ten minutes. This seems like a significant overhead.
If I am not mistaken, sorting takes place after all the data is retrieved, so altering indexes won't have much of an effect, correct? When I check the performance tab of enterprise manager, it reports problems with virtual memory paging, this should suggest I need to increase physical memory, but the operating system still reports over half of memory free.
The SGA for a database is 10G, with sga_aggregate_target set to 3G along with automatic memory management. Is this setup wrong? How can I troubleshoot? The whole system is somewhere around 30G of ram.
This is all on Oracle 11.2.0.2.0 under Windows.
Thank you for your replies.
Best regards.