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:
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