sort area size

[naushi]

Is there a formula, criteria or methos we can use to figure out what should be my sort area size. We have a 10GB Db with 100 users. We have a lot of procedures and packages running doing sorting and calculations. My buffer size and shared pool are 100 mb each.

Any advise will be appreciated. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[Guest_imported]

Keep in mind that the memory used for sort area size is outside of what the rest of your memory settings for the shared pool and buffer cache are. Meaning, it will grab additional free memory from the OS. So you really have to know how much free memory you have on your system when everything else is running.

Contrary to their original intention, sort area size and sort area retrained size should usually be set the same unless you want to run into memory allocation problems, which I have that have caused ORA-600s on partitioned index builds.

The calculation for the correct value should work in conjunction with your temp segment extent size, hash_area_size, hash_multiblock_io_count, db file multiblock read count, etc. Here are some guidelines to follow:

1. Temp segment extent size should be 4 to 5 times your sort area size, plus one block (for the header).
2. Hash area size works the same way as sort area size so it should be set the same.
3. the block size * multiblock read/io count should should be a even divisor of the temp segment extent size.
4. etc., etc.

figure the number of concurrent connections you have times the sort size you are thinking about using and ensure that there is enough extra memory on the box to support it. There is a dimminishing margin of return on increasing the sort size larger, since fewer and fewer queries or index builds will be small enough to fully fit into memory without writing to the sort segment anyway.

Hope all that helps.