DB2 Total Sorts - overflows – improve performance

28 March,2013 by Jack Vamvas

Sorts are part of processing queries , but excessive sorts , especially in an OLTP databases can kill performance. Sorts hit IO,CPU and lead to slow response time.  A typical DB2 Sort occurs if a sql statement uses an ORDER BY but can’t find a relevant index.

Sort Overflows  represent the amount of sorts overflowing to disk . A high percentage could indicate performance issues. Increasing SORTHEAP is the quickest way of improving this situation , but is probably masking underlying problems.

A more in depth approach  will require an analysis of the sql code and supporting indexes.

To obtain the sort overflow and total sorts requires the database snapshot data.


db2 get snapshot for database on MYDB
--look for Total Sorts and Sort overflows
--calculate  Sort overflows as a percentage of Total Sorts

 Read More

DB2 SORTHEAP and pinned pages - DBA DB2

DB2 Query Tuning – db2expln - DBA DB2

DB2 Tuning Toolkit – db2exfmt - DBA DB2


Author: Jack Vamvas(http://www.dba-db2.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on DB2 Total Sorts - overflows – improve performance

Comments are moderated, and will not appear until the author has approved them.

dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright