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.