Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Thumbs up Unanswered: Not enough memory available for a (private) sort heap of size 30000

    Hi Folks

    I see lots of following messages in my db2daig.log

    Not enough memory available for a (private) sort heap of size 30000
    Trying smaller size...

    2004-08-19-04.15.18.502035 Instancermm60 Node:017
    PID:13729894(db2agntp (DPR060MM) 17) Appid:*N0.prmm60.040819072836
    sort/list_services sqlsAllocateSortMemory Probe:35 DatabasePR060MM

    This is a EEE (21 nodes ) V7 fixpack8 on AIX v5

    I have SHEAPTHRES=200000
    and SORTHEAP=30000 on each node.

    I have 300 sort overflows.

    How to find out the best value for my sortheap and sortheapthres


  2. #2
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Any Clue !!!!! PLz

  3. #3
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    Figure out how many concurrent sort you have accross all partitoins. Then you'll see take that number * your sortheap to get your sheapthres. If you don't have enough memory, you may want to lower your sortheap, but the way you have it you can have only have 6 full sorts running in memory at the same time using the full amount it can grab. Try doubling your sheapthres and keep adding until it goes away from there. We have 12 partitions, or sortheap is set to 25000, and our sheapthres is set to 600000, we still have sort overflows, but it seems to be the correct balance.



    but it look like if your doing a large sort against a table, that is accross all 12 partitions, it allocating sortheap on each partitoin for just that query, that would leave only 200000/12 for each sort, but the parms is set to use more.
    David Quigley

Posting Permissions

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