Private Sort Consumes twice the temporary space than Shared Sort ? Why?

Harishkumar .Pathangay

Private Sort Consumes twice the temporary space than Shared Sort ? Why?

Hi,

I have DB2 LUW 11.1 Exp-C [also tested in 11.1.3.3 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?

thanks,

harish p

Harishkumar .Pathangay

RE: Private Sort Consumes twice the temporary space than Shared Sort ? Why?
(in response to Harishkumar .Pathangay)

Hi,

In 9.7 FP11 , it is using twice the space in temporary tablespace for both shared and private sorts. One table is constructed for sorting and other for returning rows.

In 11.1, it is using the same table in temporary tablespace to sort the data and return the results.

This is observed behavior but needs more analysis.

thanks,

harish p

Harishkumar .Pathangay

RE: Private Sort Consumes twice the temporary space than Shared Sort ? Why? [Closed}
(in response to Harishkumar .Pathangay)

Hi,

Please consider thread closed.

thanks,

harish pathangay