Summary

[ Team LiB ]

Summary In this chapter we have discussed one of the top tuning areas in DB2, Sorts. Sorts are used to return data either to online forms or applications in an ordered manner. Sometimes using a sort is the best method, but often sorts cause the biggest application performance problems. DB2 can use indexes to eliminate sorts if the columns being sorted are defined in the index in the appropriate order. DB2 v8 gives us a new DB CFG parameter, SHEAPTHRES_SHR, with which to control the amount of shared memory available for shared sorts. This gives us the ability to control shared sorts at the database level instead of at the instance level. Database snapshots provide us with details on overall sort performance at the database level. Additional snapshots are available to provide us with additional information on sorts. Connection and statement event monitors are the primary means available to us for gathering data on sort consumption and aid us in tuning and eliminating sorts. The Design Advisor and Visual Explain are primary tools for us to use in tuning and eliminating sorts. Examples were provided where significant performance improvements were made, simply by using Design Advisor and knowledge of the application, to create or modify indexes to eliminate sorts. Sort performance is probably a problem at your workplace. You can use the procedures and techniques outlined in this chapter to eliminate sorts and significantly enhance the performance of databases under your control.

[ Team LiB ]