Hi,

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.

Linda

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).

regards,

mujeeb

Mujeeb,

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?

Linda

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


regards,

mujeeb