Oracle9i introduced subpools, which naturally lead to multiple shared pool latches. The hashing-based strategy was replaced by a multiple subpools, each containing a single heap operating on a standard LRU strategy. Oracle also began standardizing memory requirement sizes, which increases the likelihood of finding an acceptable size chunk. The subpools, multiple shared pool latches, and LRU strategy dramatically reduced shared pool memory management problems. If you have managed both an Oracle8i and an Oracle9i system, you probably experienced this change and noticed quite a difference.

The number of subpools on your system can be easily determined by either looking at the instance parameter _kghdsidx_count or by counting the number of rows in x$kghlu.

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.