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