Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Flush Package Cache Dynamic - How it works

    Guys,

    We have been experiencing package cache overflow on DB2 V9.5 FP 4

    A suggestion from IBM that I should flush pacakage cache occasionally.

    I tried, did not see any difference.

    Code:
    C:\Program Files\IBM\SQLLIB\BIN>db2 select count(*) from sysibmadm.snapdyn_sql
    
    1
    -----------
           3581
    
      1 record(s) selected.
    
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 flush package cache dynamic
    DB20000I  The SQL command completed successfully.
    
    C:\Program Files\IBM\SQLLIB\BIN>db2 select count(*) from sysibmadm.snapdyn_sql
    
    1
    -----------
           3501
    
      1 record(s) selected.
    
    
    C:\Program Files\IBM\SQLLIB\BIN>
    Am I doing some thing wrong. Still getting same number of SQLs. Also sum of all statements' size is about the same.

    I am not clear about this. Please help.

    Regards

    DBFinder

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't know why they would suggest you flush package cache if you have overflow. Either increase the package cache size, or reduce the number of unique SQL statments (using statements prepared with parameter markers instead of literals in the predicate will accomplish this if you using java apps).

    When you flush package cache it flushes the access plan so that the next identical SQL statement will be recompiled, but it does not necessarily remove all the information.
    Last edited by Marcus_A; 02-09-10 at 17:15.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Also, any statements that are in use by applications obviously will not be flushed.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i View Post
    Also, any statements that are in use by applications obviously will not be flushed.
    But it "probably" does force the next user of that indentical SQL statement to recompile the access plan.

    When I do a flush package cache and then a Snapshot for Dynamic SQL, I see lots of SQL statements with 0 compiles and 0 number of executions, so that is how I know it keeps some stuff in the package cache, even if it forces the next user of that SQL to recompile. Obviously, it could be flushed completely out of memory if an active package needed it and there was no free package cache memory available.
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I just ran some tests on DB2 9.1. It looks like, after the dynamic cache is flushed, the last statement for each connection remains in the cache (even if it has completed and committed before the flush). Once a connection is reset, its last statement is also flushed. However, if another connection is made before that, and it uses the statement remaining in the cache, that statement won't be flushed after the first connection terminates. The snapshot indicates that by increasing the number of statement executions. However, it does force recompilation as the number of compilations is reset to 0.

    I used db2pd -dynamic to verify that.
    Last edited by n_i; 02-09-10 at 18:09.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Friends,

    What I think after discussions, that IBM software support has advised wrong. The flush package cache is not emptied. That suggestion was irrelevant.

    Thanks

    DBFinder

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBFinder View Post
    Thanks Friends,

    What I think after discussions, that IBM software support has advised wrong. The flush package cache is not emptied. That suggestion was irrelevant.

    Thanks

    DBFinder
    Flush package cache dynamic does force recompile of all new SQL statements. That is what it is designed to do. Whether some package information remains in memory has nothing to do with whether the cached access plan will be used next time, or whether a recompile is forced.

    The reason why the IBM advice is questionable, is that if you don't have enough memory for package cache, forcing a recompile for all subsequent SQL will not make things better. When package cache is full and a new SQL statement comes in, then DB2 forces an old package out of memory to make room for the new one, sort of the same way bufferpools work. So even if flush package cache dynamic did remove all traces of all packages from package cache memory, it is still not a good to issue that statement as some sort of cure for not having package cache enough memory (or not using prepared statements).

    The only thing worse than package cache overflow is wiping out the entire package cache memory (unless you need to do that for some reason such as a starting a new snapshot or because you want to force DB2 to select a new access plan for all SQL statements using new runstats, etc).
    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
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Marcus_A View Post

    The reason why the IBM advice is questionable, is that if you don't have enough memory for package cache, forcing a recompile for all subsequent SQL will not make things better. When package cache is full and a new SQL statement comes in, then DB2 forces an old package out of memory to make room for the new one, sort of the same way bufferpools work. So even if flush package cache dynamic did remove all traces of all packages from package cache memory, it is still not a good to issue that statement as some sort of cure for not having package cache enough memory (or not using prepared statements).

    The only thing worse than package cache overflow is wiping out the entire package cache memory (unless you need to do that for some reason such as a starting a new snapshot or because you want to force DB2 to select a new access plan for all SQL statements using new runstats, etc).
    Well, the advised this as a local fix to Package Cache Overflow problem. Since Dec 1,2009 we had 2 database crashes due to Package Cache Overflow.
    I did have doubt on this, so I called them back to double check if the local fix is right, since I did not see any difference.

    Here is their last communication regarding that PMR.

    according to your database configuration the PCKCACHESZ is set to
    20000 meaning 80MB. Your high watermark for the package cache goes
    as high as 165MB so yes this shows that you are encountering the package
    cache leak issue. Like indicated before just use the "db2 flush package cache dynamic"
    command as a workaround meanwhile. You can have it issued periodically
    until fixpack 5 or later is installed.
    My worry is/was whether after upgrade to FP5, the problem will go away or not ??

    The problem may go away, but the local fix is not relevant, I am sure.

    Thanks

    DBFinder

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBFinder View Post
    My worry is/was whether after upgrade to FP5, the problem will go away or not ??

    The problem may go away, but the local fix is not relevant, I am sure.

    Thanks

    DBFinder
    According to IBM (per your note above) there is a memory leak in Package cache that is fixed in Fixpack 5 (I presume V9.5?). If that is what they say, then I have no reason to doubt them and in that case, the temporary fix may help keep from your database crashing. But it would not necessarily help if there was no memory leak, and besides, package cache overflow is not necessarily something to be avoided at all costs.

    I would not go by your query as to determine whether the temporary fix is helping, since you cannot tell if the actual access plan is still in memory just by the number of packages in cache from your query.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Yes, I agree.

    Actually there is no other choice for us. We had upgraded from V8. 9.5.0 then 9.5.3 then 9.5.4 and now 9.5.5 next week. Each fixpack had some issue. Package Cache Overflow appeared with 9.5.4.

    When they told us to flush the package cache occasionally, we still had another crash (second one). They had said that flush helps in such case which actually proved to be untrue in our case.
    May be package cache overflow is not the root cause. Well, let us see how it goes.

    Thanks for your time.

    DBFinder

Posting Permissions

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