Re: SORT_AREA_SIZE value

The SORT_ARE_SIZE specifies the amount of memory that Oracle allocates per user process for sorting data. The SORT_AREA_SIZE is used to determine the space allocated in the main memory for each process that is performing a sort.
 Increasing the value of SORT_AREA_SIZE will reduce the total number of disk sorts which reduces disk IO. On the other hand, setting the SORT_AREA_SIZE too high can cause swapping if there is little memory left for other processes (this is slower). An example would be if the SORT_AREA_SIZE is set to 1 megabyte and 100 users processes are performing sorts then a total of 100 megabytes of memory may be allocated.

With that out of the way, the SORT_AREA_SIZE is determined by the size of your database. The default size is adequate for most online transaction processing (OLTP) operations but you may have to adjust the size for decision support systems and batch jobs.

I think that the default setting is 65536 but I have also read that the typical size for a database of 2G to 3G with 256M of memory is 102,400 (if DB_BLOCK_SIZE is 2.048) or 2,000,000 (if DB_BLOCK_SIZE is 8.192). If you are using personal Oracle then use the default size but if you are using a development, test and/or production database then the size will be determined by the size of your database and the use of the database (the way that you are using the database).

Hope this helps!!

Kevin

<shdu_at_my-deja.com> wrote in message news:9286mm$13l$1_at_nnrp1.deja.com...
> How can I determine what is a good value for SORT_AREA_SIZE
> initialization parameter.
>
> Thanks.
>
> SF
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Dec 26 2000 - 08:53:39 CST