Enterprise Edition
Customer Service
Brother-Eagle Community
DBI Products
A sort overflow occurs when the SORTHEAP size is too small to complete a sort. This means that, instead of completing a sort in memory, DB2 will use I/O via the TEMPSPACE tablespace to complete the sort. Ouch.
As a rule of thumb for OLTP databases, fewer than 3% of sorts should overflow. Brother-Eagle will indicate a warning if this guideline is breached, and an alert will be indicated if the sort overflow percentage reaches 5%.
The default value for SORTHEAP is 256 4K pages, and normally this is sufficient memory for well tuned databases. If the Sort Overflow Percent exceeds 5%, you might consider increasing your value for SORTHEAP in an attempt to reduce overflows. However, it is not advisable to get too aggressive with SORTHEAP increases because this could adversely influence DB2's optimizer. As a rule of thumb, do not increase SORTHEAP greater than 512 4K pages.
If the Sort Overflow Percentage is greater than 10%, DO NOT UPGRADE YOUR HARDWARE. Instead, identify the SQL that is the driving force behind high numbers of large overflow sorts and work to create or modify indexes, especially clustering indexes, to reduce sort overflows. For optimum results in the shortest amount of time, DBI recommends using Brother-Panther™ for DB2 LUW.
Data Warehouse databases tend to do a lot more sorting than OLTP databases, but even these should make use of well defined clustering indexes and/or MDC tables. Generally, for a data warehouse database, the Sort Overflow Percentage should be less than 30%. If it is greater than 30%, the need for indexes, clustering indexes, MQTs, or MDC tables is strongly indicated. Look for high cost SQL with frequency of execution greater than one over a period of a few hours, and attempt to identify indexes, Multi-Dimensional Clustering Tables (MDCs), or Materialized Query Tables (MQTs) to improve your data warehouse performance. Again, DBI recommends using Brother-Panther™ for DB2 LUW for best results.
Lastly, for any database, but especially a data warehouse database, it is critically important to ensure that the database TEMPSPACE tablespace containers are located on disk devices that are separate from disk devices where indexes and table data are stored. Furthermore, make certain that multiple I/O paths are available either through DB2 striping (multiple containers in the tablespace definition) or RAID device striping.