Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    18

    Unanswered: Performance issues. Please help

    I'm running my DB2 8.1 on AIX. I'm running into a memory problem and here is the error details, what i get from db2diag.log


    2004-02-24-12.15.43.676561 Instance:db2inst Node:000
    PID:565416(db2agent (CLFY81) 0) TID:1 Appid:GA19F164.OE5C.0E0334200118
    sort/list services sqlsAllocateSortMemory Probe:35 Database:CLFY81

    Not enough memory available for a (shared) sort heap of size 8
    Trying smaller size...


    Can someone please help me here. My application is hanging and we can not proceed.

    Thanks,

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: Performance issues. Please help

    Can you verify your sort related paramters like sortheap, sheapthres have correct values.
    Plus do you have INTRA_PARALLEL yes? It affects what type of sort is used - shared vs private.

  3. #3
    Join Date
    Feb 2004
    Posts
    18

    Performance issues. Please help

    Hello,

    Here are my Sort parameters:

    Sortheap : 50000
    Sheapthres_shr : 5000
    Stat_heap_size : 5000
    STMT_heap: 512
    UTIL_heap_size : 5000

    Please advice

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: Performance issues. Please help

    The values are wrong. You should have sheapthres_shr at least two times bigger than sortheap.
    Also check sheapthres at the instance level.

  5. #5
    Join Date
    Feb 2004
    Posts
    18

    Performance issues. Please help

    Thank you for your help.

    So the value for sheapthres_shr should be 100,000? should I make the change?

    Also at the instance level, If the value is low. Can I make it to 100,000?

    Thanks for all your help.

  6. #6
    Join Date
    Dec 2002
    Posts
    134

    Re: Performance issues. Please help

    I suggest you read the documentation related to the mentioned parameters. It's difficult to recommend without knowing the requirements and monitoring database performance.

    In general you need to consider the number of concurrent sorts you expect, size of the sorted data, available memory and code base (32 or 64 bit).

    The value of 50k sort pages(~200MB) may be too big, but again I do not know your environment.
    If you have a lot of relatively small sorts, just switch the values for sortheap and sheapthres_shr.

    As for instance level, please consider the number of databases you have in the instance. If it's only one, just make instance and database sheapthres_shr equal.

    You may try IBM's configuration adviser (from control center). It'll give you good values as a starting point for the parameters optimization.

  7. #7
    Join Date
    Feb 2004
    Posts
    18

    Performance issues. Please help

    Hello,

    To tell you about my environment: My database is a 1GB size database. When we run a single or couple users against this database. The performance and operations run fine.

    But when we run Win-runner with 100 users. The database reports the below listed error: Well when this 100 users connect to the database, they are doing update, insert and some select. Thats all they are doing. My application is a CRM application.

    Any suggestionss?

    Thanks:



    Error:



    2004-02-25-11.23.54.512751 Instance:db2inst Node:000
    PID:708832(db2agent (CLFY81) 0) TID:1 Appid:GA19F164.OFF4.0404D5185000
    sort/list services sqlsAllocateSortMemory Probe:35 Database:CLFY81

    Not enough memory available for a (shared) sort heap of size 7
    Trying smaller size...

    2004-02-25-11.23.54.530693 Instance:db2inst Node:000
    PID:708832(db2agent (CLFY81) 0) TID:1 Appid:GA19F164.OFF4.0404D5185000
    sort/list services sqlsCalculateSortMemory Probe:75 Database:CLFY81

    DIA8311C No memory available in the sort heap.
    ZRC=0x8B0F0033

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Performance issues. Please help

    I'm afraid, I don't have the 'direct' answer to your question ...

    But let me try ...

    Did you change the SORTHEAP and SORTHEAPTHRES_SHR as recommended earlier ? If you did, what are the values now ?

    1 GB does not seem to be a big database, espcially given that it is a CRM application ... Is it a third party software (eg . Siebel) or is it develped in house ... In the former case, get in touch with the vendor .. For the latter, you may have to do one or both of the following :

    1) Check if appropriate indexes are defined ? (You should do this even if your application is from a vendor)

    2) Tune your SQL to ensure it does not do unnecessary sorts ...

    About the second point :
    Developers normally tend to put in order by, UNION where UNION ALL will also do the same job etc (which require sort space), primarily for testing purposes .. . Do not include more columns than necessary in the SELECT clause and/or in the ORDER BY Clause .. More the columns in the ORDER BY Clause, more sort space will be needed and more CPU Time is consumed ...

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Feb 2004
    Posts
    18

    Performance issues. Please help

    Hello,

    I did changed the sort heap and now the values are:

    Sort_heap : 3000
    Stat_heap_size : 5000
    Stmt_heap: 1000
    Util_heap_size : 5000
    Sheapthres_size : 0
    DB_heap : 4000

    I still have the problem, where it reports " No memory available in the sort heap".

    dont know, whats going on. As i said earlier. My application is a CRM and we are doing a Win-runner test with 100 users.

    Does my above parameters needs any modification? MY database is 1 GB in size and has 4 GB physical memory avaiable.

    any help?

  10. #10
    Join Date
    Feb 2002
    Posts
    96
    What is your SHEAPTHRES value in dbm cfg? Why donot you increase this, and monitor server page_in/out(before and after change).

  11. #11
    Join Date
    Feb 2004
    Posts
    18

    Performance issues. Please help

    My SHEAPTHRES DBM value is 19000. But for Database its 0.

Posting Permissions

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