Query performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query performance

  1. #1
    Join Date
    Aug 2001
    Posts
    18

    Question

    Hi Guys,

    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.

    thanks
    Prasad

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    What are the SORT% parameters in the init.ora?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2001
    Posts
    18

    Question Query performance

    Hi Guys,

    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.

    Thanks
    Prasad

  6. #6
    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...
    ovidius over!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width