June 24, 2007
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,
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.database-performance.info
www.database-auditing.info
« DB2 9 Fundamentals Certification | Main | Informix User Head Count: Beyond the Numbers »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.