Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2010
    Posts
    99

    Unanswered: db2batch - wildly different fetch times during

    DB2 9.7.9a ESE running on RH5.3

    I am running the same sql statement again and again using db2batch. The strange thing is I get wildly different response times during the FETCH. Anyone have any clue what influences the FETCH phase? The pages are already in the bufferpool (i am executing the same statement over and over). Basically it can take .004 -.58 of a second to execute...not good.

    Thanks in Advance,

    James

    * Prepare Time is: 0.000260 seconds
    * Execute Time is: 0.000102 seconds
    * Fetch Time is: 0.584959 seconds
    * Elapsed Time is: 0.585321 seconds (complete)

    * 1 row(s) fetched, 1 row(s) output.

    * Prepare Time is: 0.000419 seconds
    * Execute Time is: 0.000169 seconds
    * Fetch Time is: 0.004407 seconds
    * Elapsed Time is: 0.004995 seconds (complete)

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you running it locally or remotely?

    Andy

  3. #3
    Join Date
    Nov 2010
    Posts
    99
    locally, running it on the db server

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is there anything else running on the server? Can you collect monitor data from the individual statements?

    I would expect the first iteration to take longer since it might need to read from disk. If the bufferpools are large enough to store everything for the query, the all subsequent iteration should be faster. Is this true? Do you run more than 2 iterations?

    Andy

  5. #5
    Join Date
    Nov 2010
    Posts
    99
    I've run maybe a dozen or more. The output is from within a sec of the time.

  6. #6
    Join Date
    Nov 2010
    Posts
    99
    This is on a production server so there is load.

  7. #7
    Join Date
    Nov 2010
    Posts
    99
    I'm curious why when the page is already in the bufferpool that it can still vary wildly between .004-.58 seconds for the FETCH

  8. #8
    Join Date
    Nov 2010
    Posts
    99
    basically it goes from fast to slow, then fast then slow. It's random .

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have probably already given the answer. There is additional load on the server.

    Andy

  10. #10
    Join Date
    Nov 2010
    Posts
    99
    Okay sure. Just curious what are the influences. Is there anything i can do allow more threads serviced at the same time something like this? Basically my understanding is the FETCH is just returning data from memory bufferpool). If this table was in it's own bufferpool would that help? Or is this something on the network level that I can't influence? Thanks for your quick replies btw

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Since it is local, you can eliminate the network as part of the problem.

    There is other load on the system, so you do not have total control of what is happening. If another process is utilizing the bufferpool, either through the same or different tables, then your data may be removed from the bufferpool.

    Andy

  12. #12
    Join Date
    Nov 2010
    Posts
    99
    Yes but I would think the execute time would go up if the page was flushed. But in this case the exec time doesn't change. Sorry for taking up your time.

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You obviously have some sort of contention, I just cannot diagnose it. You are going to have to determine what is happening on the server when this occurs. You need to use whatever tools you have to figure this out.

    Andy

  14. #14
    Join Date
    Nov 2010
    Posts
    99
    Got it. Thanks Andy! I'll post a solution if I find one. Just tried creating the table in it's own bufferpool with the same data and fresh reorg runstats. Unfortunately I am getting the same varying response times. Very strange.

  15. #15
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Is the RHEL5.3 a logical partition - i.e. is there virtualisation happening and so at which layer.
    Additionally are the I/O resources local or on a SAN?
    Is WLM active either in the RHEL or in DB2?
    These factors among others can cause big variations.
    Look at the user-cpu and system-cpu times for the job - not at the elapsed times.

Posting Permissions

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