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

Thread: sort_area_size

  1. #1
    Join Date
    Apr 2001
    Posts
    151
    Hi,

    My database performance is droping like a rock. Baan consultant want me to increase sort_area_size from 131072 to 524288 or 1048576. Does anyone know why Baan need so much memory?

    If I want to increase sort_area_size to what it has been suggested, any consideration I should have before doing it.

    Thank you
    Elin@trend

  2. #2
    Join Date
    Feb 2001
    Posts
    203
    SORT_AREA_SIZE
    The recommended values for this parameter fall in the range from 256KB to 4MB.

    Go to the below thread or click on search forums and type
    SORT_AREA_SIZE and see more threads on this.

    http://www.dbasupport.com/forums/sho...?threadid=9688

    Good Luck


    sree

  3. #3
    Join Date
    Apr 2001
    Posts
    151
    Thanks, Sree.

    I am printing it...
    Elin@trend

  4. #4
    Join Date
    Mar 2001
    Posts
    188
    If your performance is bad test the following
    Make the sort_area_size very great about 40MB and 100 MB if you have enough free RAM
    With this sizes you can get the fast of your db to more then 100%. I have tuned our db and only with this parameter they are 100% faster.

    Regards
    Thomas Schmidt

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Investigate ?

    Dear Elintrend, 4th May 2001 13:34 hrs chennai

    I would greatly appreciate if you could first do some preliminary Investigation and increase the sort_area_size.

    I)
    SELECT DISK.VALUE "DISK",MEM.VALUE "MEM",(DISK.VALUE/MEM.VALUE)*100 "RATIO" FROM V$SYSSTAT MEM,V$SYSSTAT DISK WHERE MEM.NAME='sorts (memory)'
    and DISK.NAME= 'sorts (disk)';

    In the above query if you find the ratio as >5% then you can well ahead go and increase the SORT_AREA_SIZE.

    You can also further drilldown for more inv in to the following areas.

    II)
    Have you sized the SORT_AREA_RETAINED_SIZE ||r to that of SORT_AREA_SIZE as for the running system.

    III)Have you created anough Temporary Tablespaces for sort purposes ?

    IV)
    i.Avoid sort operation to the possible limit.
    ii.Reduce swapping and pagging
    iii.ensure that sorting is done in memory
    iv.bypass the buffer cache for large sorts.
    v.Allocate temporary space appropriately.

    V)Performance trade-offs for large sorts

    Increasing the sort area size causes each server process that sorts to allocate more memory.It may affect O/S memory allocation and induce pagging and swapping.

    If you increase the sort area size then try to reduce the reatained size.smaller reatined size reduces memory usage but causes additional I/O to write and read data to and from temporay segments on disk.

    Well now after finding the ratio from the above query go ahead to increase the size of sort area size with some consideration for the points mentioned also.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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