Results 1 to 12 of 12

Thread: sort overflows

  1. #1
    Join Date
    May 2006
    Posts
    4

    Unanswered: sort overflows

    Hi all,

    we are experiencing a lot of sort overflows on one of our databases. We have increased the sortheap area multiple times as well as the sortheap threshold but still not seeing any improvement. The steps we have followed are as follows:

    1. investigated long running SQL's using the 'Dynamic SQL snapshot'
    found no real bad SQL's highest was one SQL statement with 77 executions and a Total execution time of 77.823921 for the total

    2. Increased the sortheap from 8192k to 16384 to 32768
    sortheap threshold is 3 times the size of sortheap

    We still expererience about 10% sort overflows of the total sorts.

    Can anyone advise on what to do here.

    thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    An index may help. Use ALLOW REVERSE SCANS when creating the index.

    BTW, what is the total sort time ?

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

  3. #3
    Join Date
    May 2006
    Posts
    4
    Total sort time is 26725722

    additional info
    *******************************************
    Total sort heap allocated = 0

    Total sorts = 85923

    Total sort time (ms) = 24434631

    Sort overflows = 73120

    Active sorts = 0

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Did you notice that 85% of the sorts overflow ?

    BTW, is it OLTP or DSS system ?

    In either environment, I guess, it is one or both of the following :

    a) Lack of required indexes
    b) Poorly coded SQL - eg. ORDER BY, GROUP BY, DISTINCT Clauses when one is not needed.

    A presentation to refer to is

    http://www.gseukdb2.org.uk/downloads/0410sortperf.zip

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

  5. #5
    Join Date
    Mar 2004
    Posts
    46
    Has the database been up for a long time?
    (Check using database snapshot - see the value for "First database connect timestamp")
    If so, it is possible that those sort numbers are accumulation over time.
    In this case, I would get the difference in sort numbers taken over a period (say a couple of hours or 1-2 days). This will give the sort values over that interval.

    Second, perform a snapshot for dynamic SQL and check the "Statement sorts" and "Statement sort overflows".
    This will give you the statements that are causing the huge sort overflows and home into the problem.

    Once again, if the DB has been up for some time and tuning has been done on statements/SQL to reduce sorts, then the true culprit statements may not become very obvious - but atleast they will give you a better picture.

    Hope that helps......

    Jayesh

  6. #6
    Join Date
    Aug 2004
    Posts
    138
    also verify if Intra_parallel is set to off..if it is on, db manager will do shared sorts instead of private sorts..indexes help you in efficient sorting

  7. #7
    Join Date
    May 2006
    Posts
    4
    Thanks people for the assistance, just one thing though, the sought overflows are about 10% and not 85%, typo error see actual values below:
    *****************************************
    Total sorts = 85923

    Total sort time (ms) = 24434631

    Sort overflows = 7312
    *****************************************
    Anyway, Intra_parallel is switched off,

    I have run dynamic sql snapshots, scanned through all the queries and could only find the 2 SQL statements below which is really working in the system. Do you think this could maybe have anything to do with the problem. Is this not just perhaps a case where the sortheap is just too small, looking at the execution times for the SQL's this does'nt look to bad.

    please advise

    ************************************************** ****
    Number of executions = 58

    Number of compilations = 1

    Worst preparation time (ms) = 18

    Best preparation time (ms) = 18

    Internal rows deleted = 0

    Internal rows inserted = 0

    Rows read = 37315774

    Internal rows updated = 0

    Rows written = 8972072

    Statement sorts = 1392

    Total execution time (sec.ms) = 138.696651

    Total user cpu time (sec.ms) = 208.578125

    Total system cpu time (sec.ms) = 45.984375
    ************************************************** ***
    Number of executions = 12

    Number of compilations = 1

    Worst preparation time (ms) = 18

    Best preparation time (ms) = 18

    Internal rows deleted = 0

    Internal rows inserted = 0

    Rows read = 8680327

    Internal rows updated = 0

    Rows written = 2457628

    Statement sorts = 288

    Total execution time (sec.ms) = 29.273176

    Total user cpu time (sec.ms) = 45.203125

    Total system cpu time (sec.ms) = 10.390625
    ************************************************** ********

  8. #8
    Join Date
    Mar 2004
    Posts
    46
    I agree that the 2 statements do not have a long time per exection (total exection time / number of executions). Thats a compliment for DB2, your hardware and/or configuration+design I guess !.

    Surprised that you are not getting the "Statement sort overflows" - see below for what I get on DB2 v8.1 fixpack 11. I would also suggest getting sort information for an interval by taking two database and dynamic sql snapshots (one at the beginning of the interval and one at the end).

    Number of executions = 2
    Number of compilations = 1
    Worst preparation time (ms) = 125
    Best preparation time (ms) = 125
    Internal rows deleted = 0
    Internal rows inserted = 0
    Rows read = 2
    Internal rows updated = 0
    Rows written = 0
    Statement sorts = 0
    Statement sort overflows = 0
    Total sort time = 0
    Buffer pool data logical reads = 4
    Buffer pool data physical reads = 0
    Buffer pool temporary data logical reads = 0
    Buffer pool temporary data physical reads = 0
    Buffer pool index logical reads = 6
    Buffer pool index physical reads = 2
    Buffer pool temporary index logical reads = 0
    Buffer pool temporary index physical reads = 0
    Total execution time (sec.ms) = 1.150543
    Total user cpu time (sec.ms) = 0.000000
    Total system cpu time (sec.ms) = 0.000000

  9. #9
    Join Date
    May 2006
    Posts
    4
    We are using DB2 7.1 fiakpak 7. I have just run another SQL snapshot and have noticed high execution and cpu times on some, looking at the stats below would any of these be seen as problem areas.


    Number of executions = 218

    Number of compilations = 1

    Worst preparation time (ms) = 20

    Best preparation time (ms) = 20

    Internal rows deleted = 0

    Internal rows inserted = 0

    Rows read = 34720021

    Internal rows updated = 0

    Rows written = 52017

    Statement sorts = 3706

    Total execution time (sec.ms) = 306.142246

    Total user cpu time (sec.ms) = 477.375000

    Total system cpu time (sec.ms) = 79.078125
    ************************************************** ********
    Number of executions = 179

    Number of compilations = 1

    Worst preparation time (ms) = 12

    Best preparation time (ms) = 12

    Internal rows deleted = 0

    Internal rows inserted = 0

    Rows read = 28481892

    Internal rows updated = 0

    Rows written = 39744

    Statement sorts = 2864

    Total execution time (sec.ms) = 355.951748

    Total user cpu time (sec.ms) = 562.515625

    Total system cpu time (sec.ms) = 110.156250
    ************************************************** *******

    Number of executions = 1677

    Number of compilations = 1

    Worst preparation time (ms) = 83

    Best preparation time (ms) = 83

    Internal rows deleted = 0

    Internal rows inserted = 0

    Rows read = 244860

    Internal rows updated = 0

    Rows written = 10080

    Statement sorts = 8385

    Total execution time (sec.ms) = 11.206797

    Total user cpu time (sec.ms) = 1.125000

    Total system cpu time (sec.ms) = 0.031250
    ************************************************** ********
    Number of executions = 5

    Number of compilations = 1

    Worst preparation time (ms) = 1

    Best preparation time (ms) = 1

    Internal rows deleted = 0

    Internal rows inserted = 0

    Rows read = 16

    Internal rows updated = 0

    Rows written = 4

    Statement sorts = 0

    Total execution time (sec.ms) = 872.522724

    Total user cpu time (sec.ms) = 0.046875

    Total system cpu time (sec.ms) = 0.000000
    ************************************************** ********

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2am
    We are using DB2 7.1 fiakpak 7. I have just run another SQL snapshot and have noticed high execution and cpu times on some, looking at the stats below would any of these be seen as problem areas.
    These are indications of a problem, but one cannot tell what the problem is without looking at the SQL statement and explain for each. You may also have excessive lockwait time that is a problem.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I suggest that you take statement event monitors ...

    My gut feel is that the sorts are not real problem with any of the four queries here ... The first two have excessive reads , third one looks OK to me and the last one, is more or less certain, it is a lock problem ...

    For the first two, analyze the access plan and you should see why there are excessive row reads ...

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

  12. #12
    Join Date
    Mar 2004
    Posts
    46
    I would let you make the judgement in the context of your application and its usage. To get the real timing, you need to divide the "Total execution time" by "Number of executions". Also, if you have a cursor-based retrieval and the query waiting for "user interaction", then the execution time may be inflated because after retrieveing a subset of the result and displaying it on the screen, the user may pause to read the data and then take action to fetch the next result set.

    Also while on the sort issue, the "Number of rows written" is an indicator of the sort overflows - in the sense that those rows are written to the temp table created during the sort overflow.

Posting Permissions

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