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?