DB2 LUW Performance: More on Locks


Posted by Scott on April 15, 2009, 9:39 pm
in General ( DB2 Performance)

First, my apologies for being away from the blog keyboard for so long. Kim Moutsos actually contacted me to see if I was still alive. Truth be known, my grandmother died, my father is in the hospital battling cancer, and I've been traveling the US States quite a bit helping companies save millions in software and hardware costs. Nonetheless, here's a quickie on some lock formulas and other updates. The good news is, I suppose, I'm accumulating a great deal of new material to share with you in future posts.
First, my apologies for being away from the blog keyboard for so long. Kim Moutsos actually contacted me to see if I was still alive. Truth be known, my grandmother died, my father is in the hospital battling cancer, and I've been traveling the US States quite a bit helping companies save millions in software and hardware costs. Nonetheless, here's a quickie on some lock formulas and other updates. The good news is, I suppose, I'm accumulating a great deal of new material to share with you in future posts.
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! DBI Logo

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