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.

An index may help. Use ALLOW REVERSE SCANS when creating the index.

BTW, what is the total sort time ?

Sathyaram

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

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

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

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

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
************************************************** ********

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

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
************************************************** ********

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.

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

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.