I have DB2 LUW 11.1 Exp-C [also tested in 22.214.171.124 ESE, same behaviour] database with shared sort [sheapthres=0,sortheap=1000,sortheapthres_shr=3000].
An example staff table with 1GB size of data is already in place with no indexes.
I run the select statement.
select * from staff where staffid > 5000 order by staffid, name, salary, netsal with ur
obviously the sort is going to spill to disk and take up temporary space. it took about 720MB of temporary tablespace before starting to return rows to the client.
Accepted. Fine. Every thing is good. Below Here is the tricky part.
I disconnected from database. Updated DBM and DB cfg to use private sort. [sheapthres=3000,sortheap=1000,sortheapthres_shr=256]
Connect to same database, run the same query. Surprisingly temporary tablespace is getting filled up with 1.4GB [twice of 720MB] for executing the query and returning results.
Why private sorts consume twice the amount of space for sorting and returning results? where as shared sort consumes only less.
The explain plans are identical with cost estimates for private sorts exactly twice than shared sorts.
There is nothing shared in terms of applications. There is only one application connected to database.
Any ideas why it is behaving this way?