This parameter is used to control the total amount of memory that can be allocated across the database manager instance for sort heaps. When the total amount of memory allocated to all sort heaps exceeds the threshold, the maximum sort heap that can be allocated to subsequent requests will be reduced.
For private sorts, this threshold is not a hard limit on the amount of memory that can be allocated to sort heaps. Additional sort heap requests are not prevented from allocating more memory than the threshold. Instead, it is a trigger point beyond which sort heap allocation amounts will be constrained.
For shared sorts, however, this threshold is a hard limit. Memory for shared sorts is preallocated when the database is started, based on the sort heap threshold. Memory allocation for shared sorts is limited by the sort heap (sortheap) database configuration parameter. As the shared memory is consumed, the sort heap allocation is reduced to satisfy the sort memory request. The total memory for shared sorts for a database, however, will not exceed the sort heap threshold. An error will occur when the memory for shared sorts is exhausted.
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
[ Top of Page | Table of Contents | Glossary | Index ]