DB2 LUW Performance: Key Cost Measures

Posted by Scott on April 7, 2009, 8:34 pm
in General ( DB2 Performance)

The secret to successful database performance tuning and optimization requires an intimate understanding of workload costs. You can become a Performance Hero in your organization by determining current workload costs, and then making physical design and configuration changes to lower transaction costs - or the costs of doing business in the database.

Too often we find database people being excessively obsessed with rates, or chasing individual queries. Rates can vary day by day depending on the day of the week, the time of the month, or the hour of the day. Successful Performance Heroes will focus on reducing transaction costs to improve efficiency. Be a hero!

Cost Measurements:

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 www.Database-Performance.info 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

Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=97