DB2 LUW: Should temporary tablespaces share bufferpools with other tablespaces? We are in the process of tuning a massive application our company has just written. Part of that tuning includes tuning the databases. We run DB2 LUW on AIX. Specifically we are at 9.7 Fix Pack 4 (although we hope to migrate to 10.1 Fix Pack 2 sometime this year).

We initially built out tablespaces only needed by the size of the data (ie, if all the tables fit in a 4K tablespace, we only created 4K tablespaces, our thought being why create tablespaces to take more disk if they are unused). This was the same for temporary tablespaces as well.

The developers have told us that they have achieved better performance by adding in a 32K temporary tablespace. So all their tables are going into 4K, 8K, or 16K tablespaces. Yet they added a 32K temporary (which has its own bufferpool) and they claim they halved some of the transaction times of their application.

When I think about it, I suppose that makes sense. I am guessing the optimizer sees the 32K space as a great place to perform joins/sorts and has more memory at its disposal being 32K vs 4K.

My fellow colleage said he (regardless of the above), he had read somewhere that DBAs should always place temporary tablespaces into their own bufferpools. When I asked him for the links to read up on it, he couldn't remember.

I am wondering...should temporary tablespaces always get their own bufferpools? Does this help with join/sort performance? Is that a good practice?

And secondly on the side, does it make sense to always create a 32K bufferpool and 32K temporary tablespaces for those join/sorts?
First of all, you must have a bufferpool that matches the page size of the tablespace. By that token if the temporary tablespace is the only one with the 32K page size then it will have the bufferpool for its exclusive use.

If you have other tablespaces with the 32K page size, only monitoring the system performance will tell you if there may be a benefit from a separate temp space bufferpool. 

You can use select * from sysibmadm.mon_bp_utilization to look at the bufferpool hit ratios and select * from table (mon_get_bufferpool(NULL,NULL)) to check page cleaner activity (POOL_NO_VICTIM_BUFFER and POOL_DRTY_PG_STEAL_CLNS should ideally be 0). If you see that a drop in the bufferpool hit ratio or high dirty page contention coincides with the temporary space use (a spike in POOL_TEMP_DATA_L_READS in select * from table (mon_get_tablespace(NULL,NULL))), creating a separate bufferpool of an appropriate size for the temporary tablespace in question might be useful.