Sorting is often required for a query, and the proper configuration of the sort heap areas can be crucial to the query's performance. Sorting is required when:
Sorting involves two steps:
How the sort is handled within these two steps results in different categories or types by which we can describe the sort. When considering the sort phase, the sort can be categorized as "overflowed" or "non-overflowed". When considering the return of the results of the sort phase, the sort can be categorized as "piped" or "non-piped".
The following situations affect the performance of sorting:
To tell if you have an overall problem with sorting, look at the total CPU time spent sorting compared to the time spent on the whole application. The database system monitor can help (see Using the Database System Monitor). In particular, the Performance Monitor (which is made up of the "Snapshot Monitor" and "Event Monitor" and is available from the Control Center), shows total sort time by default, along with other times such as I/O and lock wait.
If total sort time is a large proportion of the other times then look at the following values, which are also shown by default:
In general, make the overall sort memory available across the instance (sheapthres) as large as possible without causing excessive paging. It is possible for a sort to be done entirely in sort memory. However, if this causes the operating system to perform excessive page swapping to accommodate that sort memory you can lose the advantage of a large sort heap. So, whenever you adjust the sorting configuration parameters, use an operating system monitor to track any changes in system paging.
Note: | With the improvement in the DB2 partial key binary sorting technique to include non-integer data type keys, some additional memory is required when sorting long keys. If you believe long keys are being used, increase the sortheap configuration parameter. |
Also note that in a piped sort, the sort heap does not get freed until the application closes the cursor associated with that sort. So a piped sort can use up memory until the cursor is closed.
You can use the database system monitor and benchmarking techniques to help set the sortheap and sheapthres configuration parameters. Do the following for each database manager and its databases:
These performance variables are shown on the performance details view of the Snapshot Monitor.
If this is too difficult to determine, use 80% of the maximum sort heap
This is a recommended initial setting. You can then use benchmark techniques to refine this value.
You can also identify particular applications and statements where sorting is a significant performance problem:
Note: | You can search through the explain tables to identify which queries have sort operations. (See Appendix L, SQL Explain Tools.) |