- Configuration Type
- Database manager
- Applies to
-
- Database server with local and remote clients
- Database server with local clients
- Partitioned database server with local and remote clients
- Satellite database server with local clients
- Parameter Type
- Configurable
- Default [Range]
-
- UNIX 32-bit platforms
- 20 000 [ 250 -- 2 097 152
]
- UNIX 64-bit platforms
- 20 000 [ 250 --
2 147 483 647 ]
- OS/2 and Windows NT
- 10 000 [ 250 -- 2 097 152
]
- Unit of Measure
- Pages (4 KB)
- Related Parameters
- Sort Heap Size (sortheap)
Private and shared sorts use memory from two different memory
sources. The size of the shared sort memory area is statically
predetermined 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:
- For private sorts, this parameter is an instance-wide soft
limit on the total amount of memory that can be consumed by private sorts at
any given time. When the total private-sort memory consumption for an
instance reaches this limit, the memory allocated for additional incoming
private-sort requests will be considerably reduced.
- For shared sorts, this parameter is a database-wide hard limit on the
total amount of memory consumed by shared sorts at any given time. When
this limit is reached, no further shared-sort memory requests will be allowed
(until the total shared-sort memory consumption falls below the limit
specified by sheapthres).
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:
- Calculate the typical sort heap usage for each database:
(typical number of concurrent agents running against the database)
* (sortheap, as defined for that database)
- Calculate the sum of the above results, which provides the total sort heap
that could be used under typical circumstances for all databases within the
instance.
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: