Tuning sorting

Sorting occurs at two levels in the database. One is shared sort memory, located in the database global memory, and the other is at the agent level, called the private sort memory.

Getting ready

Monitor sorting activity on your database, as discussed in the previous chapter, by using snapshots, and compare sorting activity with the load on your database.

See how much sorting is done throughout a normal day's activity. In the following SQL, you can even identify the partition's sorting activity. You will see if there is enough sorting activity to make tuning worthwhile.

How to do it...

1. Average sort time: If average sort time per sort is long, we can start thinking about tuning.

   SELECT DBPARTITIONNUM, TOTAL_SORT_TIME / (TOTAL_SORTS + 1)
   FROM   SYSIBMADM.SNAPDB;
   

2. Sort time per transaction: If the value of SortsPerTransaction is greater than five, there might be too many sorts per transaction:

   SELECT DBPARTITIONNUM, TOTAL_SORTS / ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS + 1)
   FROM ...