Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003

    Unanswered: SORTHEAP & Overflows


    I'm on DB2 8.1 (FP5) on AIX (ML5) on a 32bit system.

    I have a question regarding how to set the optimal size for SORTHEAP to avoid sort overflows and sorts in general.

    After i've execute an sql, with only 1 active agent against the databse, I've used the command "db2 get snapshot for dbm |grep ort" with the output:
    Private Sort heap allocated = 0
    Private Sort heap high water mark = 117462
    Post threshold sorts = 9
    Piped sorts requested = 11148
    Piped sorts accepted = 11148

    My SORTHEAP = 12288 (4K) and SHEAPTHRES = 82000 (4K).

    Firstly, the 'Private sort heap high water mark ' - how is this being allocated when my SORTHEAP is only 12288.

    Also, it appears that the 'private sort heap allocated' is always less than the SORTHEAP, that is it is always only about = 100 (even when there is only 1 active agent).

    I've run explain against this SQL, and calculated the max(ROWSxROWWIDTH) to obtain what i would have thought to be the max SORTHEAP size. So i then configure this on the database and rerun the SQL and once again, i get sort overflows as the 'private sort heap allocated' is only around 100.

    As you can see i appear to be going round in circles so if anyone can point out the missing link it would be much appreciated.


  2. #2
    Join Date
    Mar 2004
    To determine the sort heap threshold is being reached ,take

    get snapshot for db|grep -i ""Post threshold sorts"

    compare this with total no of sorts.

    if the allocation of sort heap exceeds the sort heap threshold , a piped sort cannot be performed and any request for a piped sort will get rejected.
    In that case a sort heap will be allocated to handle the sorting but it will be with reduced size.

    get the snapshot and use

    % of piped sorts =(piped sorts accepted / piped sorts requested) * 100

    The no of piped sort requests rejected

    piped sorts rejected = piped sorts requested - piped sorts accepted.

    use udf function snapshot_dbm to get these informations.

    The total amount of private sort heap can be find

    get snapshot for dbm | grep -i "Private Sort heap allocated"

    also determine the sort operations per sort because of SHEAPTHRES_SHR and SHEAPTHRES

    using commit statments attempted and rollback statements attempted.

    Increase by 10% taking care of threshold value.for private sort the sort heap threshold is an instance-wide soft limit.for shared sorts the sort heap threshold is a database wide hard limit(it starts rejecting the connection).



  3. #3
    Join Date
    Aug 2003

    Thanks for you reply.

    But I don't have any post threshold sort problems, all my piped sorts are accepted.

    What i really need to know is how to determine the sortheap per SQL. I have a statement and it is constantly experiencing 'sort overflows' so how do i determine the optimal size? Can you determine this from explain? Or am i left to execute the sql, watch the 'private sort heap allocated' and keep increasing the SORTHEAP (and SHEPTHRES) until there are no more overflows?


  4. #4
    Join Date
    Mar 2004
    1. create event monitor stmt_mon for statments,connections
    write to table buffersize 4 blocked manual start.

    2. turn it on during the period you want to monitor that application.

    set event monitor stmt_mon state 1

    3. flust event monitor stmt_mon

    4. set event monitor stmt_mon state 0

    5. select from connection_stmt_mon table to find the agent id that is causing that problem

    6. use that agent_id to select from statment_stmt_mon table to find the
    statment text.

    please check the table name



Posting Permissions

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