The Sorting component monitors the sort heap areas currently allocated for the database or partition. A sort heap area is the memory that the database manager allocates to an application when a sort is requested. Sorting is often required during query execution. Additionally, some SQL statements require multiple, concurrent sorts, each requiring dedicated sort heap. Therefore, configuring sort heap areas is critical to query performance. Sort heap is controlled by the SHEAPTHRES database manager configuration parameter, which sets the sort heap threshold for the entire instance, and by the SORTHEAP database configuration parameter, which sets the sort heap size per sort for the database.
At each refresh, Spotlight captures the following statistics and displays them for the Sorting component.
Pct of Thresholds—The percentage of the sort heap threshold currently allocated to the database as defined by the SORT_HEAP_ALLOCATED monitoring element captured by DB2.
Overflows—The ratio of the number sorts and hash joins that ran out of sort heap space to the total sorts and hash joins that were executed. Increasing the SHEAPTHRES database manager configuration parameter for the instance or the SORTHEAP database configuration parameter for the database can reduce the overflow rate. For more information, see Sort/Join Overflow Percentage alarm.
Click the Sorting component to open the Databases drilldown. This drilldown provides details about the database or partition you are monitoring. The Sorting tab on the drilldown is in immediate focus to help you pinpoint problems related to sort activity and sort heap usage in the database.