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,
* 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)
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?
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
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.
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.
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.
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.