Oracle does place a hard limit on the number of subpools. In Oracle Database 11g, I was able to start an instance with seven subpools, but with eight subpools, the instance did not start-in fact, it required a shutdown abort before it could be restarted.

Interestingly, Oracle does not have to respect your subpool number wishes. In fact, in the example shown in Figure 7-13, the instance parameter was set to 2 and the instance restarted, yet Oracle created three subpools. And without the instance parameter manually set, Oracle created only a single subpool. So while you can influence Oracle, it still reserves the right to make changes.

Figure 7-13. Shown is the OSM script, spspinfo.sql, which shows basic information about the number and size of the shared pool subpools. In this example, three subpools exist.