A query is taking to run for 22 sec if I added an order by clause, without order by clause it is returning in less than a second. What could be the problem, can anybody explain please.
What are the SORT% parameters in the init.ora?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
This might be due to the sort operation happening on disk rather than memory. Try again after increasing your sort_area_size to the maximum possible, which is limited by the available RAM on your machine. Make sure you don't page SGA out of 'real' memory.
They say Oracle's sorting algorithm is not really great, and if possible, (eg in data warehouse application), try and insert data in a pre-sorted fashion, using OS to sort data before inserting in Oracle.
Very probably it is like this:
Your query without ORDER BY is returning you the first few rows to your front-end and that's why you have the impression it is so quick. But if you let it return all the rows to the very last one you would probably be surprised that it is not realy so quick as it seemed in the beginning.
With ORDER BY, your query does have no choice but to first process all the rows, then sort them, and only then it is able to return the first set of rows to your front end. But this doesn't mean that the sorting is realy a bottleneck for this query.
But of course, as others have suggested, it is also possible that your sorting is happening on disk, which might realy be a bottleneck.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Thanks for all the feedback. How do you check for the disk sorts, and what % of SGA must be allocated for sort_area_size and what value should be set for sort_multiblock_read_count. The SGA size is 60 MB and sort_area_size is 2 mb and sort_multiblock_read_count is 2. There is no more memory available on this machine. Any feedback is appreciated.
Why don't you try the old method: take a snap, run the query, take a snap, run spreport, look after sort area statistics?
You know, using performance manager is not so easy when you run after a query that runs for only 22 secs...
Click Here to Expand Forum to Full Width