What are Subpools?

In Oracle 9i and later versions, the Shared Pool can be divided into subpools.  Each subpool is a "mini" shared pool, having its own set of Free Lists, memory structure entries, and LRU list.  This was a scalability change made to the Shared Pool/Large Pool to increase the throughput of these pools in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared/Large Pool for a single latch as in earlier versions.  The reserved area for the Shared Pool is divided equally throughout the subpools as well.

When you get an ORA-04031, the trace will indicate the subpool where the error occurred.

For example,
ORA-04031: unable to allocate 4192 bytes of shared memory ("shared pool","SELECT /*+ FIRST_ROWS */ * F...","sql area (6,0)","kafco : qkacol"):4031:375:2008:ocicon.c

In this case, the sixth subpool is where the error occurred

The downside to using subpools is that there are cases where one subpool can get over-utilized. Once the subpool is selected, the search for memory chunks can fail even though another subpool might have adequate memory available. Starting with 10g, we do have functionality allowing the search to "switch" to another subpool if a memory request is not met in the selected subpool but that is not possible for all memory structures/elements.


Unbalanced use of the subpools can lead to ORA-04031.  We see this commonly with the memory allocation failures in the  "session param values" memory structure.  With 9i and higher, dynamic parameter settings are stored for each configured process and a subpool is selected at startup to manage all "session param value" entries.  If the PROCESSES parameter is set very high and you do not have high concurrent connections, this can cause unnecessary permanent memory allocations in this subpool and can lead to ORA-04031 problems.  Generally, the performance gains from having multiple Shared Pool latches outweigh the possible problems associated with over-utilized subpools.

An end-user has no visibility into subpools.  They are hidden below the implementation of the Shared/Large Pools.   NOTE: If the Shared Pool is using subpools, there will automatically be subpools created in the Large Pool as long as LARGE_POOL_SIZE>0.

Reference (this problem appears to have spanned many bugs):
Bug 4184298 - Subpool imbalance for "session parameters"  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3637/viewspace-664656/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3637/viewspace-664656/