Sort Pool Status Monitor report
Command ====> _______________________________________________ Scroll ===> CSR_
More: +
BMCSftwr.PMDSRTP1 -- SORT POOL STATUS MONITOR -- 06/19 15:18:54
---< HELP >--------------------< *ALERT* >----------------------< CONFIG >---
Actions: ( + ) A - ADVICE H - History
DB2: DHY2 13.1 Interval start: 2021-06-19-15.05.01 Duration: 00:13:54
+ Sort pool merge/pass efficiency 94%
Sort work file database name DHY2
Maximum sort pool size (per thread) 260 K
Number of sort merge passes . . . . . . . . . . . . . . . 6
Number of workfiles requested for merge passes . . . . . 32
Average number of workfiles requested per merge pass . . 5
Number of inefficient sort merge passes . . . . . . . . . 0
Number of workfiles denied for merge passes . . . . . . . 0
Average number of workfiles denied per merge pass . . . . N/P
Maximum number of workfiles concurrently allocated . . . 115
Number of times the work buffer pool did not have enough
buffers to support the allocated workfiles at runtime 0
Because there is no way to determine the number of successful sorts that Db2 performed from Db2 statistics, sort performance is primarily restricted to analysis of sorts that spill over to work-file processing. The sort pool size is a limit of the amount of private DBM1 storage that each thread can allocate to perform an internal quick sort. These are the most efficient sorts.It is preferable to perform the majority of minor query sorting in this manner. Unfortunately, it is difficult to determine the ideal size. A very large limit can threaten DBM1 stability, because a sudden spike in concurrent large sort requests might cause a virtual storage shortage and a subsequent crash. The only way to approximate sort pool performance without running performance traces is to monitor the number of sorts that are overflowing to work file merge-pass processing. If an increase in the size of the sort pool significantly decreases the number of merge-pass runs, the increase is worthwhile. But you need to consider the impact of the potential maximum concurrent requests, and keep the size of the sort pool small enough to contain it.
Although sorts that run within the sort pool are the most efficient, large sorts that spill over to work files can be very efficient if the work buffer pool is large enough to support the number of work files requested without significant I/O. Because work file processing is highly sequential, Db2 calculates the number of work files allowed by dividing the number of prefetch pages (pool size multiplied by the sequential steal threshold) by 16. As work files are used by concurrent processes, Db2 reduces the number that are available by subtracting double the number that are used (in an effort to prevent sort failures due to insufficient buffers).
The primary tuning opportunity for large spillover sorts is to define the work buffer pool with a high (100%) sequential steal limit and enough pages to handle the maximum concurrent work file demand without significant I/O. Similarly, you can set the write thresholds very high to avoid ever writing data to DASD. However, if your sorts are so large that they cannot be contained in the work buffer pool, it is better to set the write thresholds low enough to optimize write engine efficiency with a steady flow of write I/O operations. You must interpret the data on this report in conjunction with the associated work buffer pool.