Database Reference
In-Depth Information
■
Note
I was using manual shared memory management in this example!
This is a relatively important change as you go from Oracle9
i
and before to 10
g
. In Oracle 10g, the
SHARED_POOL_SIZE
parameter controls the size of the shared pool, whereas in Oracle9
i
and before, it was just the
largest contributor to the shared pool. You should review your 9
i
and before actual shared pool size (based on
V$SGASTAT
) and use that figure to set your
SHARED_POOL_SIZE
parameter in Oracle 10
g
and above. The various other
components that used to add to the size of the shared pool now expect you to allocate that memory for them.
Large Pool
The large pool is not so named because it is a “large” structure (although it may very well be large in size). It is so
named because it is used for allocations of large pieces of memory that are bigger than the shared pool is designed
to handle.
Prior to the introduction of the large pool in Oracle 8.0, all memory allocation took place in the shared pool.
This was unfortunate if you were using features that made use of “large” memory allocations, such as shared server
UGA memory allocations. This issue was further confounded by the fact that processing, which tended to need a
lot of memory allocation, would use the memory in a manner different from the way the shared pool managed it.
The shared pool manages memory on an LRU basis, which is perfect for caching and reusing data. Large memory
allocations, however, tend to get a chunk of memory, use it, and then be done with it. There was no need to cache
this memory.
What Oracle needed was something similar to the recycle and keep buffer pools implemented for the block buffer
cache, and that's exactly what the large pool and shared pool are now. The large pool is a recycle-style memory space,
whereas the shared pool is more like the keep buffer pool—if people appear to be using something frequently, then
you keep it cached.
Memory allocated in the large pool is managed in a heap, much in the way C manages memory via
malloc()
and
free()
. As soon as you “free” a chunk of memory, it can be used by other processes. In the shared pool, there really
was no concept of freeing a chunk of memory. You would allocate memory, use it, and then stop using it. After a while,
if that memory needed to be reused, Oracle would age out your chunk of memory. The problem with using only a
shared pool is that one size doesn't always fit all.
The large pool is used specifically by:
•
Shared server connections
, to allocate the UGA region in the SGA.
•
Parallel execution of statements
, to allow for the allocation of interprocess message buffers,
which are used to coordinate the parallel query servers.
•
Backup
for RMAN disk I/O buffers in some cases.
As you can see, none of these memory allocations should be managed in an LRU buffer pool designed to
manage small chunks of memory. With shared server connection memory, for example, once a session logs out,
this memory is never going to be reused so it should be immediately returned to the pool. Also, shared server UGA
memory allocation tends to be “large.” If you review the earlier examples with the
SORT_AREA_RETAINED_SIZE
or
PGA_AGGREGATE_TARGET
, you'll remember that the UGA can grow very large and is definitely bigger than 4KB chunks.
Putting shared server memory into the shared pool causes it to fragment into odd-sized pieces and, furthermore, you
will find that large pieces of memory that will never be reused will age out memory that could be reused. This forces
the database to do more work to rebuild that memory structure later.
The same is true for parallel query message buffers, since they are not LRU-manageable. They are allocated and
can't be freed until they are done being used. Once they have delivered their message, they are no longer needed and
should be released immediately. With backup buffers, this applies to an even greater extent—they are large, and once
Oracle is done using them, they should just “disappear.”
