Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2008
    Posts
    44

    Unanswered: troubleshooting slowness

    A SQL that used to run in four seconds now taking 5.5 seconds. The query response time started increasing gradually like 4.1 second on day 1, 4.3 seconds on day 2, 4.4 seconds on day 3...and now it is running for 5.5 seconds in two weeks. I would like to find out the reason for the increase. I need help in where to start looking.

    There was no change in hardware, data volume, system config, instance config, db config,etc. I have history of snapshots (dynamic sql, bufferpool, database, etc.) data available in a seperate database, so, I can look back.

    Any pointers on where to start looking are warmly welcomed! Thanks!!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try reorging the table(s) involved in the query.
    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
    Dec 2008
    Posts
    44

    reorg-runstat didn't help

    Thanks for the reply Marcus, Reorg and Runstat did not help, still seeing the same pattern. Any other thing I can look? Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did the access plan for the query change?

    Also, how do you measure query execution time? If it is end-to-end from the client application or directly in DB2? In e2e scenarios, you may have changes in the application causing delays or maybe network issues.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2008
    Posts
    44
    Thanks stolze!
    Comparision of Explain plans between before and after the issue shows no difference in the path. Path showed index scans only before and after. This SQL is used as benchmark SQL, so, I collect all the information on this SQL.

    Query execution time is from dynamic sql snapshot, so, it not the client application value (so, I can eliminate network factor)

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you have any statements being executed concurrently? If you have the full snapshot, you can compare the detailed numbers to see where time is being spent more now.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2008
    Posts
    44
    I am observing weird pattern lately, slow query occurs only during specific days. Also, during the slow response days, query response time increases as the day progress.

    For example,
    Every week Thursday the query response time gradually increases from 4.1 to 4.2, 4.3,4.4,...till 6 seconds as the day progresses. And, every week Wednesday, the query response time is well below threshold (eg. below 3 seconds) all day.

    I would like to get suggestion from this forum, I am sure some of you might have come across this situation.

    Thanks for inputs.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You wouldn't happen to be stopping and starting DB2 on Tuesday night Wednesday morning would you? Is there som process that only runs on the slow days?
    Dave

  9. #9
    Join Date
    Dec 2008
    Posts
    44
    no, DB2 is running the whole week, db2stop and db2start happens once in a week on Sunday.

    There is no difference is code between slow vs regular days, and there is no special jobs run either

    Thanks for reply

Posting Permissions

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