SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-26-2008, 06:17 PM
| ||||
| ||||
 Re: Exorbitant execution time I asked a DB2 expert about the execution time of random physical reads of DB2 V8.1 on Windows. He answered that on good hardware even DB2 on Windows also nearly reaches 10 ms. So, most probably, my problem is not caused by physical reads... I was thinking this before. But I have no idea, what else is causing this long total execution time. Any ideas? Johannes Lebek wrote: > Hi there, > > lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some queries took a very long time. A snapshot discovered the following: > > Number of executions = 47 > Number of compilations = 1 > Worst preparation time (ms) = 2 > Best preparation time (ms) = 2 > Internal rows deleted = 0 > Internal rows inserted = 0 > Rows read = 192277 > Internal rows updated = 0 > Rows written = 0 > Statement sorts = 47 > Buffer pool data logical reads = 76306 > Buffer pool data physical reads = 155 > Buffer pool temporary data logical reads = 0 > Buffer pool temporary data physical reads = 0 > Buffer pool index logical reads = 2995 > Buffer pool index physical reads = 16 > Buffer pool temporary index logical reads = 0 > Buffer pool temporary index physical reads = 0 > Total execution time (sec.ms) = 123.065931 > Total user cpu time (sec.ms) = 0.546875 > Total system cpu time (sec.ms) = 0.062500 > Statement text = SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13 FROM TABLE1 WHERE (COL6 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AND COL12 = 'N' > > Total execution time was 123 seconds, although user and system cpu time had been reasonable values. > There is an index on COL6 and COL12 (in this order; maybe, (COL12, COL6) is faster). > 155 physical reads is not that bad (0.2%). Assumed these are all random reads, is 120 seconds a reasonable number on a Windows machine? This equals 775 ms per random read. I learned random reads take about 10 to 20 ms on a z/OS box. > However, I cannot imagine that all physical reads are random reads. If there are sequential reads, the execution time of random reads must be even longer. > > What are your experiences? Do you think, this execution time is caused by the physical reads? If not, what are your assumptions? > Thanks for your help, > > Johannes >      |
Linear Mode
