Figure 7-13 shows a series of SQL statements related to shared pool subpools. In this case, a 752MB shared pool exists with three subpools. The x$ksmss query returns one row for each subpool plus another row if the Java pool exists. The instance parameter to set the number of subpools, _kghdsidx_count cannot be altered dynamically. If you want to influence Oracle to invoke a subpool number change, you must set the instance parameter and recycle the instance.

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.