Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2014
    Posts
    7

    Unanswered: DB2 v9.7 : Why sort overflow occurs when SORTHEAP is AUTOMATIC

    Dear All,

    DB2 v9.7 : Why sort overflow occurs when SORTHEAP is AUTOMATIC ? There is 5% overflow occurs, but post threshold is 0. So I was puzzled why sort overflow occurs in first place though SORTHEAP is set to AUTOMATIC?

    OS : AIX (64 bit)
    STMM : ON
    SORTHEAP : AUTOMATIC
    All other memory parameters : AUTOMATIC

    Please advise. Thank you.

    Regards
    GGK

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ggk View Post
    Dear All,

    DB2 v9.7 : Why sort overflow occurs when SORTHEAP is AUTOMATIC ? There is 5% overflow occurs, but post threshold is 0. So I was puzzled why sort overflow occurs in first place though SORTHEAP is set to AUTOMATIC?

    OS : AIX (64 bit)
    STMM : ON
    SORTHEAP : AUTOMATIC
    All other memory parameters : AUTOMATIC

    Please advise. Thank you.

    Regards
    GGK
    What are the current values (get db cfg for ... show detail) of SHEAPTHRES_SHR and SORTHEAP?
    --
    Lennart

  3. #3
    Join Date
    Jun 2014
    Posts
    7
    Hi,

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(33481291)
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(607345)
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(99)
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(565432)
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(18874537)
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(3765441)
    Database heap (4KB) (DBHEAP) = AUTOMATIC(2321)

    DB snashot:
    Total sorts = 29467065
    Sort overflows = 1214206
    Post threshold sorts = 0


    Since its set to AUTOMATIC, I expect , there should not be any sort overflows.
    But still sort overflow is happening. I dont know why...
    If anyone can help me, I would greatly appreciate.

    Thank you.
    GGK

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ggk View Post
    Hi,

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(33481291)
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(607345)
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(99)
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(565432)
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(18874537)
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(3765441)
    Database heap (4KB) (DBHEAP) = AUTOMATIC(2321)

    DB snashot:
    Total sorts = 29467065
    Sort overflows = 1214206
    Post threshold sorts = 0


    Since its set to AUTOMATIC, I expect , there should not be any sort overflows.
    But still sort overflow is happening. I dont know why...
    If anyone can help me, I would greatly appreciate.

    Thank you.
    GGK
    I would try to make SHEAPTHRES_SHR a multiple of SORTHEAP. For example

    SORTHEAP=3765441
    SHEAPTHRES_SHR=5*3765441=18827205
    --
    Lennart

  5. #5
    Join Date
    Jun 2014
    Posts
    7
    SORTHEAP & SORTHEAP_sHR both are set to automatic. How do you make that ?

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ggk View Post
    SORTHEAP & SORTHEAP_sHR both are set to automatic. How do you make that ?
    You need to assign them a fixed value, i.e. they will not be automatic anymore. Example:

    db2 update db cfg for ... using SORTHEAP 3765441
    db2 update db cfg for ... using SHEAPTHRES_SHR 18827205

    Not saying that it will guarantee that your overflows will go away, but it might be worth a shot.
    --
    Lennart

  7. #7
    Join Date
    Jun 2014
    Posts
    7
    Thank you for your reply.

    But I would like to know, why the overflow occurs though we set everything to automatic.

    It can grep memory as much required automatically .

    If still overflow occurs, what is the point in automatic setting ?

    Thanks

    GGK

  8. #8
    Join Date
    Aug 2008
    Posts
    147
    A more in depth approach will require an analysis of the sql code and supporting indexes.
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Small sorts can overflow if the optimizer underestimates the sort memory size for a plan. Even though the sort heap limit is large, the query will request memory according to the optimizer estimates, and when that turns out to be insufficient, instead of requesting additional memory the sort will spill.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jun 2014
    Posts
    7
    Thanks n_i. That makes sense. But any idea, how to overcome it completely?
    Thanks to Lennart & Jack too.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ggk View Post
    how to overcome it completely?
    Make sure the optimizer estimates are closer to reality -- if those spills are indeed the biggest performance problem you have.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Jun 2014
    Posts
    7
    Ok great, thanks n_i. Will try to tune to use more optimized path.

  13. #13
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ggk View Post
    Thank you for your reply.

    But I would like to know, why the overflow occurs though we set everything to automatic.

    It can grep memory as much required automatically .

    If still overflow occurs, what is the point in automatic setting ?

    Thanks

    GGK
    From what I hear automatic works great for some, and not so great for others. A common strategy is to start with automatic and when things have stabilized turn it off and do fine tuning by hand. I also get the impression that automatic works better and better with each new version, so perhaps we'll all be out of business within the next few versions ;-)
    --
    Lennart

  14. #14
    Join Date
    Jun 2014
    Posts
    7
    Thanks Lennart !!

    Yeah looks like , if we set to automatic, we can't have less pressure on tuning.

    Also the problem is, once we set to automatic in prod, it's nearly impossible to change to manual as it involves so many layer of approval and you can't justify management.

    They expect it to be auto tuned (that was the impression given by IBM marketing). So if I try to explain then for approval, they think either I don't know db2 or I don't know the use of auto setting.

    Let's hope new versions are easier..

    Thanks again for all.

    Regards
    GGK

  15. #15
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by n_i View Post
    Small sorts can overflow if the optimizer underestimates the sort memory size for a plan. Even though the sort heap limit is large, the query will request memory according to the optimizer estimates, and when that turns out to be insufficient, instead of requesting additional memory the sort will spill.
    hi,
    As far as i kown,This behavior has been changed when db2 v9.7 comes out。db2 engine will try to allocate more sort memory at execution time when it found the optimizer underestimated the sort memory requirement.
    But there is just i have been told and no official doc or links have describe this and i can't prove it either

Posting Permissions

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