Hi Guys,


I was wondering what would be the best way to collect some data/statistics without impacting the system where I can generate a report showing the amount of cpu consume/spent only to satisfy locking requests. I'm working on a Websphere/DB2 environmemt and I found that most of the critical apps are using isolation level REPEATABLE_READ. I'm sure this is causing performance issues apart from forcing db2 to consume more memory and cpu to hold those locks longer due the isolation level. Any ideas would be highly appreciated.

Thanks.

harbry

I doubt that CPU time is significant for the locking, but lock wait time and elapsed time would definitely be factors. You can see both of these in a snapshot for dynamic SQL.

I suppose if the elapse time is extended cpu and resources also be use/kept for longer. Once I noticed for one thread that the locks held according to our monitor was 750,000 locks. In the database server at the moment what you can see is only %CPU User comsuption. There is not %WAITS %SYSTEM comsuption shown by nmon.

nmon will not help you on this. If you do a DB2 snapshot for dynamic SQL, you can see how much elapsed time each SQL statement consumed in lock-wait time, elapsed time, CPU time, etc. DB2 does not consume a significant amount of CPU time when a SQL statement is in lock-wait mode.

Thanks Mate!!!!