Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    australia
    Posts
    17

    Unanswered: tracking down %cpu usage spent on locking

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Location
    australia
    Posts
    17
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Location
    australia
    Posts
    17
    Thanks Mate!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •