At times it can be noticed that after a tablespace is created over a database partition group, following that the size of the SQLSPCS files on some of the partitions will grow significantly, whereas on the other partitions there will be only a marginal growth in the size of the SQLSPCS file.
When a tablespace is created across multiple partition node, then the size by which the SQLSPCS file grows on each node is calculated as follows :
'Size of the largest SQLSPCS file amongst all nodes' + Size required to store information about the newly created tablespace.
This behavior can be demonstrated by the following Test Scenario and monitoring the size by which the SQLSPCS file grows on each partition after executing each step below :
1. Create a Tablespace which spans across all the partition nodes,
2. Create a Tablespace in the partition group which spans a single partition node.
3. Again create a Tablespace in the partition group which spans across all the partition nodes
Resolving the problem
This behavior can be avoided by :
First creating all those tablespaces which will span all the partitions and then creating the tablespaces individually on separate partition(or smaller partition groups), so the SQLSPCS will then grow only on those nodes.