Private and shared sorts use memory from two different memory sources. The size of the shared sort memory area is statically predetermined (and not preallocated) at the time of the first connection to a database based on the value of sheapthres. The size of the private sort memory area is unrestricted.
The sheapthres parameter is used differently for private and shared sorts:
Examples of those operations that use the sort heap include: hash joins and operations where the table is in memory.
Explicit definition of the threshold prevents the database manager from using excessive amounts of memory for large numbers of sorts.
Recommendation: Ideally, you should set this parameter to a reasonable multiple of the largest sortheap parameter you have in your database manager instance. This parameter should be at least two times the largest sortheap defined for any database within the instance.
If you are doing private sorts and your system is not memory constrained, an ideal value for this parameter can be calculated using the following steps:
(typical number of concurrent agents running against the database) * (sortheap, as defined for that database)
You should use benchmarking techniques to tune this parameter to find the proper balance between sort performance and memory usage.
You can use the database system monitor to track the sort activity.
For more information see: