Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

    Unanswered: stmtheap and pckcachesz

    Can an increase in stmtheap db cfg value cause the package cache to overflow (adm4500w)? Both stmtheap and pckcachesz are not automatic.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If only indirectly. Larger statement heap will allow you to process longer queries, for example, which will take up more space in the statement cache when compiled.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    This was my initial thought too. Larger statement heap will allow to process more complex statements, but I'm not sure why they would take up more space in the package cache. This is assuming the workload / statement text hasn't changed (and it hasn't according to the customer). Also, SQL is not complex.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    n_i, You were right.

    Response from the expert:
    "It's possible that the more complex queries result in more complex access plans which take up more space in the package cache, but I wouldn't generally tie a larger stmtheap with a requirement to increase the package cache."


    But if SQL is not complex, why would the access plan change and the statement text is exactly the same as per the customer.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post


    But if SQL is not complex
    I suspect the optimizer may have a different opinion than you (or the client) as to what constitutes a complex statement.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    The SQL I saw (only several statements) looked pretty simple (to me). Also, the manual states:

    "Statement Heap Size (stmtheap)
    Although the size of the statement heap does not influence the optimizer in choosing different access paths, it can affect the amount of optimization performed for complex SQL or XQuery statements."

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The way I read it, the value of STMTHEAP by itself does not influence the optimizer decision (unlike SORTHEAP, for example), however, additional memory will allow it to consider more optimization options, which indeed can result in a different plan.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    It does make sense.

    They have a prod mirror env where explains were performed on some SQL before/after the statement heap change and there was no difference in the plans.


    After increasing the statement heap, they started getting package cache overflows as well as high CPU and lock-waits. No diagnostic info was collected prior to reverting the changes back.


    My theory about lock-waits. Please let me know what you think:
    increase in stmtheap -> increase in amount of optimization -> increase in compile time -> increase in V (variation) lock duration => lock-waits on V (exclusive) lock.

    This variation lock is held in exclusive mode when the statement is compiled / loaded into the cache. So, other applications that need to execute exactly the same statement will have to wait until it's compiled / loaded.

    The lock-waits could have been on user / catalog tables so V-lock waits is just my theory.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    They have a prod mirror env where explains were performed on some SQL before/after the statement heap change and there was no difference in the plans.


    After increasing the statement heap, they started getting package cache overflows as well as high CPU and lock-waits.
    Somehow it does not add up.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Bella, if you still have those explains. check the stats before and after. I wonder if they were changed.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    I got an update this morning that the problem (high cpu / lock-waits / package cache overflow / etc...) happened again with no change to the statement heap (change was undone last week). So, an increase in stmtheap doesn't seem to related to this perf / cache overflows issue.

    I also checked with our optimizer expert (previously it was a package cache expert) and he doesn't think pckcachsz needs to be increased when increasing stmtheap. DB2 can generate a more optimal plan, but it should not be more complex (if previously db2 had to drop down to greedy join enumeration due to hitting statement heap limit).

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

  13. #13
    Join Date
    Oct 2007
    Posts
    246
    hi bella,

    optimization of statement also depends on the level of optimization wht we set, or as u said increase of the stmtheap results in more optimization of the query ??
    redgs
    Paul

Posting Permissions

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