LCKMS - The Average Lock Wait Time
LCKMS = Time database waited on locks (ms) / Lock waits
Use 'dbsnap2.txt' or a database snapshot as input to the above formula.
Not every lock times out. Some locks just experience temporary delays while they wait for required resources to become available. The LCKMS formula will tell you the average lock wait time. LCKMS should not be greater than LOCKTIMEOUT, but it could be equal to LOCKTIMEOUT if ALL of your locks time out. Remember, too, that LOCKTIMEOUT is configured in seconds and this formula computes milliseconds - another one of the wonderful consistencies within DB2.
LCKTX - The Average Lock Wait Time per Transaction
LCKTX = Time database waited on locks (ms) / TX Count
Use 'dbsnap2.txt' or a database snapshot as input to the above formula. TX Count = the sum of Commits Attempted plus Rollbacks Attempted.
This formula will tell you the average amount of time, in milliseconds, that each transaction spends waiting on locks.
As you work to tune your databases, as you improve the physical design to improve statement performance, hopefully you will also be able to measure reduced times for LCKMS and LCKTX. Reduced lock times is a "side benefit" of improving statement performance - the faster statements run, the less opportunity they have to collide or contend with one another.
The Shameless Marketing Moment
Monday 3 March, I am presenting at DUGS - Database Users Group Sydney. The presentation is on "DB2 9 Autonomic Tuning" - What does DB2 9 autonomically tune, how best to deploy autonomic tuning, and what is left for the DBA to do (yes, you still have a job).
5-7 March, I'll be attending and presenting at IDUG AP in Sydney at the Cliftons. DBI is a Platinum sponsor. I'm giving a session "Extreme Makeover: DB2 LUW Physical Design : DB2 Edition" wherein we cover case studies, important formulas and methodologies, plus Index Design, MDCs, and the obligatory slide on Compression. DBI will also have a VSP session, and I'm giving an Ed Seminar on Friday - "DB2 LUW Performance Diagnosis Lab". I look forward to seeing many of you in these sessions and the vendor exhibit area.
Looking a little further ahead, DBI will be at IOUG (the Independent Oracle User Group) in April - if you are a DBA with both DB2 LUW and Oracle on your resume, be sure to visit our booth and check out DBI's performance tools for Oracle.
In May, I sure hope to see you at IDUG in Dallas TX. This is IDUG's 20th conference, and it will also be my 20th. You can catch the Ed Seminar and "Extreme Makeover" sessions in Dallas if you miss them in Sydney. And, of course, DBI will be sponsoring this IDUG event in a big way...
Just for Fun
Spice up your life. Are you aware of the health benefits of cayenne pepper?
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info
Your Performance IS Our Business
Please Note: DBI has moved to a new web address!
