Global Sort/Join panel
The Global Sort/Join component monitors the sort heap areas currently allocated for all active databases in the instance and returns a number value for all items in the panel. The following illustration is an example of the Global Sort/Join panel:
A sort heap area is the memory that the database manager allocates to an application when a sort is requested. Sorting is often required for a query. 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.
Required monitor switch
Sort
Displayed counters
At each refresh, Spotlight captures the following counts and displays them for the Global Sort/Join component on the instance home page. You can use the Metric Editor to customize thresholds for the metricsbehind these counters to generate appropriate alarms.
-
Post Threshold Sorts—The total number of sorts requests received after the sort heap threshold has been reached. (These sorts are allocated a sort heap smaller than the SORTHEAP value.) A large number in this field might indicate a need for a larger SHEAPTHRES value or for fewer or smaller sort requests.
-
Post Threshold Hash Joins—The total number of hash join requests limited by concurrent use of shared or private sort heap space. A large value in this field could be an indication that SHEAPTHRES needs to be increased.
-
Piped Sort Rejects—The total number of piped sort requests that were rejected across all databases. Piped sorts can reduce I/O and thus improve sort performance. A piped sort occurs when sorted information is returned directly to the application without requiring a temporary table to store a final, sorted list of data. A piped sort is rejected if the sort heap threshold is exceeded at the time the sort heap is allocated. Increasing the SHEAPTHRES for the instance or the SORTHEAP for the database can increase piped sort acceptance.
-
Sort Requests—The total number of executed sorts. This number includes both sorts and post threshold sorts.
-
Active Sorts—The total number of current sorts that have a sort heap allocated.
-
Sort Overflows—The total number of sorts that may have used disk space for temporary storage because they ran out of allocated sort heap space.
-
Hash Joins—The total number of hash joins executed.
-
Hash Loops—When a single partition of a hash join is larger than the available sort heap space, the join is said to loop. To correct this problem consider increasing your sort heap size. This counter records the total number of times hash loops have occurred.
-
Hash Join Overflows—The number of times hash joins exceeded available sort heap space. If the number of Hash join small overflows is too large relative to the Hash join overflows value, try increasing your sort heap size.
-
Hash Join Small Overflows—The number of times hash joins exceeded available sort heap space by a small amount (less than 10% of the available sort heap). Increase SHEAPTHRES If this field and Hash join overflows contain high values. If the number of Hash join small overflows is too large relative to the Hash join overflows value, try increasing your sort heap size.
-
Sort Heap Allocated—The amount of sort heap (in kilobytes) allocated for all sorts during the last monitoring interval. The amount of memory allocated for each sort can be some or all of the available sort heap size. (Sort heap size is the amount of memory available for each sort, as defined by the value for the database configuration parameter SORTHEAP.)