Total Sort Heap Allocated

Description:  The total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.

Usage:  The amount of memory allocated for each sort may be some or all of the available sort heap size. Sort heap size is the amount of memory available for each sort as defined in the database configuration parameter sortheap.

It is possible for a single application to have concurrent sorts active. For example, in some cases a SELECT statement with a subquery can cause concurrent sorts.

Information may be collected at two levels:

• At the database manager level, it represents the sum of sort heap space allocated for all sorts in all active databases in the database manager
• At the database level, it represents the sum of the sort heap space allocated for all sorts in a database.

Normal memory estimates do not include sort heap space. If excessive sorting is occurring, the extra memory used for the sort heap should be added to the base memory requirements for running the database manager. Generally, the larger the sort heap, the more efficient the sort. Appropriate use of indexes can reduce the amount of sorting required.

You may use the information returned at the database manager level to help you tune the sheapthres configuration parameter. If the element value is greater than or equal to sheapthres, it means that the sorts are not getting the full sort heap as defined by the sortheap parameter.