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.
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.
I am printing it...
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.
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.
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.
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 ?
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.