Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Performance: Key Cost Measures

April 7, 2009, 8:34 pm
Posted by Scott in General
Using a database snapshot (refer to "dbsnap2.txt" from your preparation materials) as input, compute the following:

Total Number of Transactions (TXCNT): TXCNT = Commit statements attempted + Rollback statements attempted

The number of SELECTS per Transaction (SELTX): SELTX = "Select SQL statements executed" / TXCNT

SELTX indicates how much data retrieval work is being done for each transaction. A value less than 10 is common and desirable. The higher your value above 10, the more critical it becomes that the database workload is optimally tuned. Also, as SELTX increases, so too does the risk of lock contention.

The number of INSERTS, UPDATES, and DELETES per Transaction (DMLTX): DMLTX = "Update/Insert/Delete statements executed" / TXCNT

DMLTX indicates how much data change activity is being performed for each transaction. A value less than 4 is common and desirable. As DMLTX increases, this will influence the need to increase the DB CFG parameter LOGBUFSZ. The risk of lock contention also increases along with increases in DMLTX.

The number of SORTS per Transaction (SRTTX): SRTTX = "Total Sorts" / TXCNT

SRTTX is critically important to achieving performance optimization and becoming a Performance Hero. While you may not be able to directly influence SELTX and DMLTX, you can certainly use physical design techniques to lower SRTTX. Removing Sorts from your transactions will measurably improve transaction response times AND lower CPU consumption. By lowering CPU consumption, you restore capacity to the current hardware and may avoid costly, unnecessary hardware upgrades.

Shameless marketing moment:

On July 10, 2007, at 10:00AM CDT, DBI is hosting a Webinar "A Preview to DBI Performance Solutions". DBI will present key features of Brother-Panther and Brother-Eagle Enterprise Edition and demonstrate how these breakthrough performance tools can be used to rapidly solve performance problems, improve efficiency and transaction response times, and potentially save your organization thousands in avoidable, unnecessary hardware upgrades. Please visit to register. One lucky participant will be chosen at random to win a $50 Best Buy gift certificate!

Just for Fun:

Contemplate this thought: Failure is impossible until you quit trying.

Until next time,

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant

Printer friendly