Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Question Unanswered: How to disable query caching?

    Hey,
    Can't find the answer to this anywhere.
    I'm trying to optimize some queries but DB2 (8.2) keeps caching the results and this messes up my tests.
    Is there any way of disabling or skipping the caching of a query?
    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    THere is no way to disable it, but you can tell the system to remove what is there. Look at the "FLUSH PACKAGE CACHE DYNAMIC" statement.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    "Flush package cache dynamic" only empties 'package cache'. If you would like to 'flush' all caches (for example bufferpools etc) stop the db2 database with command:
    db2stop force
    db2start

    I hope you are testing on test environment. If not then don't look into my advice.

    BTW, it is not usual to stop caching in production environment, because database can run much slower. So this is not the case of IBM to support such behavior of DB2.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by costinb
    Hey,
    Can't find the answer to this anywhere.
    I'm trying to optimize some queries but DB2 (8.2) keeps caching the results and this messes up my tests.
    Is there any way of disabling or skipping the caching of a query?
    Thanks
    If you are talking about caching of data in bufferpools, forget about trying to disable that for tests. With a normal application, some data is expected to already be in the bufferpool.

    For TPC-H tests (see www.tpc.org) they require that each query be run 10 times and then calculate an average run time for each query. Even with nothing else running on the system, the run times can vary quite a bit during the 10 tests of each query. But the averages are fairly predictive of the performance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by grofaty
    Hi,
    "Flush package cache dynamic" only empties 'package cache'. If you would like to 'flush' all caches (for example bufferpools etc) stop the db2 database with command:
    db2stop force
    db2start
    Careful: this will stop the DB2 instance and not just a single database
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    Careful: this will stop the DB2 instance and not just a single database
    Stolze, I agree. I was just assuming there is a test computer with test database on it. That is how the testing system should be designed...
    Grofaty

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Running a query right after starting an instance is the most unrealistic test you could possibly devise. DB2 bufferpools are an integral part of the database, and running a query on an empty bufferpool is of no use in the real world of performance tuning or benchmarking.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Marcus_A, the main question was how to empty 'caches'...

    But I have seen many SQLs that running from bufferpool executed in second and running from disk (not in bufferpool) run for 5 minutes. This logic is specially seen if federated database is used and using two databases on different platform (federated server is moving a lot of data from source to target database). Stopping and starting a local and remote database is final test I always do to know if transactional SQL is working efficient.

    Regards,
    Grofaty

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    Running a query right after starting an instance is the most unrealistic test you could possibly devise. DB2 bufferpools are an integral part of the database, and running a query on an empty bufferpool is of no use in the real world of performance tuning or benchmarking.
    It really depends on what you want to do. I did this (recycling DB2 instance) quite often to measure physical disk I/O, for example. Granted, such numbers cannot be applied to product systems, but can be very helpful to derive formulas or whatever you need...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Dec 2005
    Posts
    3
    Thanks
    Yes, all the work is being done on a test system.
    The thing is the large queries are usually not performed by the same client, so for them it's like having no cache (by the time the next client issues a large query, db2 already invalidates it in the cache). That's why I must verify how the queries behave not when ran 10 times, as an average, but only once.
    I thought I could avoid restarting DB2 but seems I can't escape it after all :P
    Peace

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by costinb
    Thanks
    Yes, all the work is being done on a test system.
    The thing is the large queries are usually not performed by the same client, so for them it's like having no cache (by the time the next client issues a large query, db2 already invalidates it in the cache). That's why I must verify how the queries behave not when ran 10 times, as an average, but only once.
    I thought I could avoid restarting DB2 but seems I can't escape it after all :P
    Peace
    You never said if you wanted to flush the package cache (the compiled SQL query and access plan) or flush the data cache (the bufferpools).

    Flushing the bufferpools for each test is not a good idea unless you have a poorly configured system. Even a very large database should have a bufferpool hit ratio above zero if there are separate bufferpools dedicated to small tables and small/medium indexes which should not ever get flushed out of cache by a table scan of a large table (which should be placed in the different bufferpool). Bufferpool resources are shared, and not specific to a client.

    If you are talking about package cache, someone already told you how to flush that without a restart.

    Based on what you have said so far, the chances of your test coming up with any results that correlate to the real world are close to zero. Any extrapolations or conclusions you make are probably invalid.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Dec 2005
    Posts
    3
    I was talking about the package cache. Meanwhile I found out it can be flushed only on DB2 versions 8 and higher.
    Thanks

Posting Permissions

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