could not fathom sort behavior - why 40MB is so critical?

Harishkumar .Pathangay

could not fathom sort behavior - why 40MB is so critical?

Hi,

I have a database with sheapthres=0[dbm] sortheap=1000[db] sheapthres_shr=3000.

I have a temporary tablespace with dedicated buffer pool size 9279 pages of 4K each.

The query "  select * from staff where staffid > 5000 order by staffid, name, salary, netsal with ur" will work properly and return results. In Temporary table space the sort is spilled into disk and occupies 1245425664 Bytes.

So for so good.

Change temporary tablespace with dedicated buffer pool size to 9278 pages of 4K each.

The same query will return error. File System Full. I analysed why File System is getting Full. The reason is it creating two temporary tables of 1245425664 Bytes[SQL000002.TDA,SQL000003.TDA]. Obviously Hard Disk is not having 2.5GB free space, hence the issue.

But Why sort behaves this way? I monitored sort monitor elements, post threshold sorts is 0, piped sorts is also 0.

How come One Page of Buffer pool will double up my space requirements? That is extremely hard to understand.

thanks,

HP

Harishkumar .Pathangay

RE: could not fathom sort behavior - why 40MB is so critical?
(in response to Harishkumar .Pathangay)

Hi,

I altered the configuration a bit. It is behaving differently. There is intricate relationship between sort heap and temporary space buffer pool size. based on which additional space can be conserved or occupied in temporary tablespace.

update db cfg sort heap 3000 and temporary space buffer pool size is 3050. run the query, it will be able to return results after sorting as expected. it will create one temporary table in "temp space1"

update db cfg sort heap 2950 and temporary space buffer pool size is 3050. run the query, it will be able to return results after sorting by consuming double space in temporary table space. it will create two temporary table in "temp space1".

How to avoid duplicate temporary table space consumption in sorts of reasonably large table, with out indexes.

thanks,

harish pathangay

David Williams

could not fathom sort behavior - why 40MB is so critical?
(in response to Harishkumar .Pathangay)
If there is not enough memory to sort with one spill to disk it will need to spill to disk twice.

Regards,
David.

> On 14 March 2019 at 08:49 "Harishkumar .Pathangay" <[login to unmask email]> wrote:
>
>
> Hi,
> I altered the configuration a bit. It is behaving differently. There is intricate relationship between sort heap and temporary space buffer pool size. based on which additional space can be conserved or occupied in temporary tablespace.
> update db cfg sort heap 3000 and temporary space buffer pool size is 3050. run the query, it will be able to return results after sorting as expected. it will create one temporary table in "temp space1"
> update db cfg sort heap 2950 and temporary space buffer pool size is 3050. run the query, it will be able to return results after sorting by consuming double space in temporary table space. it will create two temporary table in "temp space1".
> How to avoid duplicate temporary table space consumption in sorts of reasonably large table, with out indexes.
> thanks,
> harish pathangay
>
>
> -----End Original Message-----

Harishkumar .Pathangay

could not fathom sort behavior - why 40MB is socritical?
(in response to David Williams)
Thanks for the reply.
Let me provide more information. I have a table with 5 columns and sample data up to 1GB in size loaded into the table.
My Sort Heap is 3000 = 12MB approx. In DB CFG.
Obviously it has to spill multiple times. It cannot sort them entire 1GB in the sort heap which is just 12MB. It is spilling to temporary table space containers and creates temporary table of 1GB in size [call it TEMP02]. I do not have issue with that.
After sort is just about to complete, just before returning rows to caller, it is creating another temporary table of 1GB [TEMP03] once this second table is complete the first 1GB table [TEMP02] is deleted. The second temporary table is just created for the purpose of returning rows in sorted order.
However, if I increase the buffer pool page, then I can see that it creates only one temporary table in temporary table space containers and it is just returning rows from that directly.

There is a sweet spot configuration involving buffer pool and sort heap by which I can avoid the additional table creation. I could not understand what is that sweet spot?

I am using shared sort, so piped or non-piped is not significant.

Thanks,
Harish P

Sent from Mail for Windows 10

From: David Williams
Sent: 14 March 2019 20:07
To: [login to unmask email]
Subject: [DB2-L] - RE: could not fathom sort behavior - why 40MB is socritical?

If there is not enough memory to sort with one spill to disk it will need to spill to disk twice.

Regards,
David.

> On 14 March 2019 at 08:49 "Harishkumar .Pathangay" <[login to unmask email]> wrote:
>
>
> Hi,
> I altered the configuration a bit. It is behaving differently. There is intricate relationship between sort heap and temporary space buffer pool size. based on which additional space can be conserved or occupied in temporary tablespace.
> update db cfg sort heap 3000 and temporary space buffer pool size is 3050. run the query, it will be able to return results after sorting as expected. it will create one temporary table in "temp space1"
> update db cfg sort heap 2950 and temporary space buffer pool size is 3050. run the query, it will be able to return results after sorting by consuming double space in temporary table space. it will create two temporary table in "temp space1".
> How to avoid duplicate temporary table space consumption in sorts of reasonably large table, with out indexes.
> thanks,
> harish pathangay
>
>
> -----End Original Message-----

-----End Original Message-----