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/