DB2 Total Sorts - overflows – improve performance

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