IBM Books

Administration Guide


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:

Different Types of Sorting

Sorting involves two steps:

  1. A sort phase
  2. Return of the results of the sort phase.

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".

Overflowed and Non-Overflowed
If the information being sorted cannot fit entirely into the sort heap (a block of memory that is allocated each time a sort is performed) it overflows into temporary database tables. Sorts that do not overflow always perform better than those that do.

Piped and Non-Piped
If sorted information can return directly without requiring a temporary table to store a final, sorted list of data, it is referred to as a "piped sort". If the sorted information requires a temporary table to be returned, it is referred to as a "non-piped sort". A piped sort always performs better than a non-piped sort.

Tuning the Parameters that Affect Sorting

The following situations affect the performance of sorting:

Looking for Indicators of Sorting Performance Problems

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:

Percentage of overflowed sorts
This variable (on the performance details view of the Snapshot Monitor) shows the percentage of sorts that overflowed. If the percentage of overflowed sorts is high, increase the sortheap and/or sheapthres configuration parameters if there were any post-threshold sorts. (To determine if there were any post threshold sorts, use the Snapshot Monitor.)

Post threshold sorts
If post threshold sorts are high, increase sheapthres and/or decrease sortheap.

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.

Techniques for Managing Sorting Performance

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:

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 N. "SQL Explain Tools".)

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]