DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: USE_MERGE Hint

  1. #1
    Join Date
    Aug 2000
    To tune the SQL statement , I use USE_MERGE hint and
    found the performance is dramatically getting better
    (around 50% better) .
    Before doing that , I change the sort_area_size to 2MB
    (the default is 64K in Oracle73) , I have some doubts on that
    actually :
    1. I think sort area size is additional to SGA and it's created
    for each session if the sort is required . So is there any problem if many session request sorts and the server memory
    would be used up . ( the server has 1GB memory and oracle SGA is around 300 MB now ) .
    2. How to manage the sort_area_retain_size ? in my case ,
    should I set it to a very small value like 0 or just keep the
    default value 64K .


  2. #2
    Join Date
    Aug 2000
    correct my typing mistake is previous thread , the sort_area_size is changed to 20MB not 2 MB

  3. #3
    Join Date
    Jun 2001
    Helsinki. Finland
    The default value is not always 0. If you do not explicitly set sort_area_retained_size, Oracle uses the value of the sort_area_size parameter.

    20M is very big sort_area_size. Some people consider 1M being huge.

    To reduce I/O operations set sort_multiblock_read_count to sort_area_size/sort_area_retained_size.

    We have very badly written application, with lots of groups by and havings, etc. I have tried to set sort_area_size to 10M and to 1M, I noticed there was no big need for 10M sort_area_size. Even 1M is a lot. What for do you need 20M?

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