What is the shared pool ?
Oracle keeps SQL statements, packages, object information and many other items in an area in the SGA known as the shared pool. This sharable area of memory is managed as a sophisticated cache and heap manager rolled into one. It has 3 fundamental problems to overcome:
The unit of memory allocation is not a constant - memory allocations from the pool can be anything from a few bytes to many kilobytes.
Not all memory can be 'freed' when a user finishes with it (as is the case in a traditional heap manager) as the aim of the shared pool is to maximize sharability of information. The information in the memory may be useful to another session - Oracle cannot know in advance if the items will be of any use to anyone else or not.
There is no disk area to page out to so this is not like a traditional cache where there is a file backing store. Only "recreatable" information can be discarded from the cache and it has to be re-created when it is next needed.
Error: ORA 4031
Text: unable to allocate %s bytes of shared memory (%s,%s,%s)
-------------------------------------------------------------------------------
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the
DBMS_SHARED_POOL package to pin large packages,
reduce your use of shared memory, or increase the amount of
available shared memory by increasing the value of the
initialization parameters SHARED_POOL_RESERVED_SIZE and
SHARED_POOL_SIZE.
If the large pool is out of memory, increase the initialization
parameter LARGE_POOL_SIZE.
The issue can be worked around with next actions:
1. disable durations by setting "_enable_shared_pool_durations"=false or disabling ASMM i.e. setting SGA_TARGET to 0. The impact with disabling durations is that the shared pool cannot shrink effectively negating that element of auto memory management.
- AND / OR -
2. increase the shared pool size (using SHARED_POOL_SIZE, SGA_TARGET or MEMORY_TARGET depending on memory configuration)
