"Norbert Munkel" <nm@acoreus.de> wrote in message
news:mP**************@se2-cb104-9.zrh1.ch.colt.net...
Hi Mark,
Mark A wrote:
The TPC-H benchmarks stipulate that each query be run 6 times (I believe)
to get an average execution time (although it is slightly more
complicated than that). There are 22 queries in the TPC-H benchmark.
www.tpc.org
Right. Thank you for pointing me there. My point is: Does it ,
economically spoken, make sense to do benchmarks to this extent except for
marketing reasons?
The reason that the TPC-H benchmark requires 6 executions of each query is
precisely because the cache can make a difference from one execution to the
next, so an average score is needed. Probably 3 times is fine for most
purposes. But if you do clear the cache and bufferpools each time, then it
should be about the same each time.
But clearing the cache is not always ideal for analysis purposes, because
DB2 is meant to run with some data cached, which is more realistic than
clearing the cache every time. This is more of an issue if small, frequently
used tables and indexes, and the system catalog are put in a separate
bufferpool from the bigger tables.
Another issue is that there can be a big difference in execution time if
multiple queries are run at once. This is especially true if system
temporary tablespaces and other resources are not large enough.
One more thing. When I ran the TPC-H benchmark, I noticed that about 1/3 of
the queries ran MUCH faster with query optimization level of 7 (default is
5). This is especially true for very complex decision support queries. I
believe that IBM ran their benchmark with 7. Optimization level of 7 is
usually not appropriate for OLTP systems because of the extra overhead of
the SQL optimization required.