Empty Query Cache

[UnixSlaxer]

Hello,

Running a query for the first time on DB2 takes a fixed amount of time.
But when query is executed for the second time, the amount of time is
usually less since the query is (most probably) cached already.

I would like to clear out the DB2-UDB 8.2 query cache (I want the
previous execution time again).

Any advice would be appreciated.

Thanks.

Regards,
Salem

[Mark A]

Re: Empty Query Cache

"UnixSlaxer " <unixsmaxer@hot mail.com> wrote in message
news:1118126121 .802881.285180@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Hello,
>
> Running a query for the first time on DB2 takes a fixed amount of time.
> But when query is executed for the second time, the amount of time is
> usually less since the query is (most probably) cached already.
>
> I would like to clear out the DB2-UDB 8.2 query cache (I want the
> previous execution time again).
>
> Any advice would be appreciated.
>
> Thanks.
>
> Regards,
> Salem
>[/color]
There are two different cache's. One is the package cache which has the
execution plan. You can clear this with flush package cache.

The other is the bufferpool(s) that cache the tables and indexes (and system
catalog). The only way to clear it out is to run other large queries that
forces the old data out of the bufferpools, or more reliably, you can use
the activate database and deactivate database command.

Without any connections to the database, attach to the instance and activate
the database. Then connect to the database and run the query. The terminate
all connections to the database (db2 terminate for each connection), and
deactivate the database. Then to run the query again, repeat the steps above
(the bufferpools will be empty each time).

I am not sure why you are doing this, since DB2 is designed to use
bufferpools extensively to reduce synchronous I/O.

[UnixSlaxer]

Re: Empty Query Cache

Thank you for your response.

I am doing this for performance and experimental evaluation.

Regards,
Salem


Mark A wrote:[color=blue]
> "UnixSlaxer " <unixsmaxer@hot mail.com> wrote in message
> news:1118126121 .802881.285180@ o13g2000cwo.goo glegroups.com.. .[color=green]
> > Hello,
> >
> > Running a query for the first time on DB2 takes a fixed amount of time.
> > But when query is executed for the second time, the amount of time is
> > usually less since the query is (most probably) cached already.
> >
> > I would like to clear out the DB2-UDB 8.2 query cache (I want the
> > previous execution time again).
> >
> > Any advice would be appreciated.
> >
> > Thanks.
> >
> > Regards,
> > Salem
> >[/color]
> There are two different cache's. One is the package cache which has the
> execution plan. You can clear this with flush package cache.
>
> The other is the bufferpool(s) that cache the tables and indexes (and system
> catalog). The only way to clear it out is to run other large queries that
> forces the old data out of the bufferpools, or more reliably, you can use
> the activate database and deactivate database command.
>
> Without any connections to the database, attach to the instance and activate
> the database. Then connect to the database and run the query. The terminate
> all connections to the database (db2 terminate for each connection), and
> deactivate the database. Then to run the query again, repeat the steps above
> (the bufferpools will be empty each time).
>
> I am not sure why you are doing this, since DB2 is designed to use
> bufferpools extensively to reduce synchronous I/O.[/color]

[Norbert Munkel]

Re: Empty Query Cache

Hi,

UnixSlaxer wrote:
[color=blue]
> Thank you for your response.
>
> I am doing this for performance and experimental evaluation.[/color]

well. File system caching could be an issue as well, as long as you have
not created all tablespaces with "no file system caching", direct IO or
raw devices.

Your disk controllers may do some caching as well.

What I try to say: Difficult to compare just by flushing the databases
caches. The second run could give a different execution time, either.

I would probably try it the opposite way, but that´s just a guess.

How about creating a script which takes care of _filling_ the
bufferpools with data ,run it before each of your tests and
deactivate/activate db in between?

Could be closer to your real life scenario and you would not need to
reboot your server between the tests.

Depends on your actual needs, of course. Never tried to benchmark my
applications like this.

just my 2 ct´s


Norbert

[Mark A]

Re: Empty Query Cache

"Norbert Munkel" <nm@acoreus.d e> wrote in message
news:d84tpf$6li $01$1@news.t-online.com...[color=blue]
>
> well. File system caching could be an issue as well, as long as you have
> not created all tablespaces with "no file system caching", direct IO or
> raw devices.
>
> Your disk controllers may do some caching as well.
>
> What I try to say: Difficult to compare just by flushing the databases
> caches. The second run could give a different execution time, either.
>
> I would probably try it the opposite way, but that´s just a guess.
>
> How about creating a script which takes care of _filling_ the bufferpools
> with data ,run it before each of your tests and deactivate/activate db in
> between?
>
> Could be closer to your real life scenario and you would not need to
> reboot your server between the tests.
>
> Depends on your actual needs, of course. Never tried to benchmark my
> applications like this.
>
> just my 2 ct´s
>
> Norbert[/color]

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.

https://www.tpc.org

[Norbert Munkel]

Re: Empty Query Cache

Hi Mark,

Mark A wrote:
[color=blue]
> 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[/color]

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?

If I like to figure out how to tune up my application, I spend most time
by analyzing access paths, thinking about what I can do to get better
ones and look at the snapshot data of the tests to verify my
assumptions. If I can´t see something which could save me at least about
1/2 hour of processing time a day, It is probably not worth working on it.

just my 2 ct´s

Norbert

[Mark A]

Re: Empty Query Cache

"Norbert Munkel" <nm@acoreus.d e> wrote in message
news:mPype.31$_ P6.782@se2-cb104-9.zrh1.ch.colt. net...[color=blue]
> Hi Mark,
>
> Mark A wrote:
>[color=green]
>> 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[/color]
>
> 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?
>[/color]

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.