shared pool 结构

The SGA is comprised of fixed areas like the Log Buffers and the Buffer Cache(s) as well as memory pools (Shared Pool, Large Pool, Java Pool, and in 10g, the Streams Pool).  Memory allocated to the various pools are divided in heaps that can be composed of one or many sub heap memory structures. 

This is similar to many segments inside a tablespace. The tablespace will be the heap and the segments are the subheaps. The extents within the segment are like the various subheaps that can exist inside "parent" subheaps.   The goal in the shared memory areas is the same as a tablespace--avoid fragmentation.  To do this we allocate the chunk of memory that best fits the request for memory, coalescing adjacent free space as needed and detecting memory that can be flushed and reused.

Inside these pools, we use free list buckets.   They are structures of free lists and each list correspond to a specific size. Oracle does a binary search on the free list sizes to find the appropriate free list.  The first bucket that is greater or equal to the requested size will be returned.  At startup of the database, there are various sized chunks of memory created in each pool.  We will continue to walk the free list until we find a bucket which points to a large enough extent of memory.  The Shared Pool will utilize a Least Recently Used (LRU) algorithm to "age out" memory structures that have not been reused over time.

To get a better idea, see this information from a heapdump trace showing the free list bucket summary:

Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48

This shows that bucket 1 has 443 chunks of memory where the maximum size is 40 bytes and the average is 40 bytes. Bucket 2 is a free list of memory chunks with sizes between 40 and 48 bytes. When a chunk of space is freed/deallocated, it is added to the bucket whose size is less than or equal to the chunk’s size.  If you find smaller chunks of memory available in the Shared Pool vs. a mixture of large chunks and small chunks, this can indicate the application is causing fragmentation and there is a increasing chance that a future large memory requests will fail with an ORA-04031 error.  See
Permanent chunks - These chunks can be allocated in different ways.   Some chunks are allocated 
and will remain in use for the "life" of the instance.   Some "permanent" chunks are allocated but can 
be used over and over again internally as they are available. 

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"  

What is the Reserved Area?

A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache.  For this reason, the Shared Pool should be sized to ensure that frequently used data is cached.  If there is not enough free space in the Shared Pool, then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold latch resources for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.

By default, Oracle configures a small Reserved Pool (or Reserved Area) inside the Shared Pool. This memory can be used for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects.  After the memory allocated from the Reserved Pool is freed, it returns to the Reserved Pool.

5% of the Shared Pool is set aside as the Reserved Pool to handle allocations of memory higher than defined by the hidden parameter _shared_pool_reserved_pct.  Issue

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '_shared_pool_reserved_pct';

to see the current setting in your environment.  In some application environments, 5% is too small.  If an ORA-04031 problem indicates a very large memory request failure, increase the size of the Reserved Area by manually setting SHARED_POOL_RESERVED_SIZE or if using SGA_TARGET>0, change the hidden parameter, _shared_pool_reserved_pct to 10 or 15 (see example below) to ensure that when the Shared Pool grows or shrinks automatically, the Reserved Area will change as well. 

Warning:
If you decide to use the SHARED_POOL_RESERVED_SIZE parameter and SGA_TARGET>0, you may experience ORA-4031 situations on large memory requests because the MMAN process may be increasing the size of the Shared Pool over time, while the Reserved Area remains constant.   Thus the Reserved Area may start out at 10% of the Shared Pool size, but over time it may end up 2% or 3% (much smaller than expected in relation to the new Shared Pool size).

The Reserved Area handles bigger memory allocations in an attempt to decrease the chances of  fragmentation in the Shared Pool over time.

SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile

or add this in the pfile

"_shared_pool_reserved_pct"=10

For large allocations, Oracle attempts to allocate space in the Shared Pool in the following order:

   1.  From the unreserved part of the shared pool. 

   2.  From the reserved pool. If there is not enough space in the unreserved part of the Shared Pool, then Oracle checks whether the reserved pool has enough space. 

   3.  If there is not enough space in the unreserved and reserved parts of the Shared Pool, then Oracle attempts to free enough memory for the allocation.  It then retries the unreserved and reserved parts of the Shared Pool.  These mini-flushes will clean out RECREATABLE/FREEABLE memory chunks from the LRU list that have not been reused for a while.

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

转载于:http://blog.itpub.net/8135069/viewspace-681415/