Most components of the Shared Pool are shared among sessions.
A notable distinction between the Shared Pool and the buffer cache (which is also shared between sessions) is that the shared pool primarily caches metadata while the buffer cache stores data.
Size of the shared pool
The size of the shared pool depends on whether automatic shared memory management (ASMM) and Automatic Memory Management (AMM) is used or not and might depend on the value of shared_pool_size (See MOS Note 455179.1)
Examining the contents of the Shared Pool
The following query shows the summed up allocations of memory for various components in the Shared Pool:
select
name,
to_char(bytes/1024/1024, '999,999,990.00') mb
from
v$sgastat
where
pool = 'shared pool'
order by
bytes desc;
While the previous query shows summations, more detailed information about individual objects in the Shared Pool can be queried from
The heap manager allocates and releases memory for/from the Shared Pool and keeps track of allocations in the Shared Pool.
When the heap manager is requested to allocate a certain amount of memory, it tries to locate a chunk of contiguous memory to satisfy this request.
If such a contiguous space cannot be found, a process begins to free batches of heaps from the shared pool until the chunk can be allocated.
If even after trying five times to find free memory by going trhough the LRU list there is still not enough contiguous memory left after freeing the memory still (typically because the objects are being used, see pinned objects), Oracle throws the error message ORA-04031: unable to allocate … bytes of shared memory.
Pinned objects
The heap manager does not free memory associated with objects that are being used.
Such objects are referred to as pinned objects.
An object can be pinned manually using the dbms_shared_pool package.
LRU list
The Shared Pool uses a LRU list to determine which memory can or should be freed if memory becomes sparse.
This is different from the Large Pool which does not have such a LRU list.
SQL Statements
All child versions of SQL statements that are stored in the Shared Pool can be queried in v$sql.
The child versions' work area can be queried from v$sql_workarea.