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.
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.
Jurij Modic 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.