Oracle: Shared Pool

(Sub-) components of the Shared Pool are

Library cache (stores executable SQL and PL/SQL)
Reserved pool (to allocate large (>5 KB) contiguous chunks of memory
Data dictionary cache
Server result cache (for SQL query result cache)
Other components (enqueues, latches, ILM (Information Lifecycle Management) bitmap tables, Active Session History (ASH) etc.)

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.

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

v$db_object_cache and its related views
v$sql and v$sqlarea

Allocating memory / Heap manager

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.

Cleare the shared pool

Remove all information from the shared pool:

alter system flush shared_pool;

Queries

The units of memory stored in the shared pool are exhibited through x$ksmsp:

select * from x$ksmsp;

Problems arising from an undersized pool

If the Shared Pool is sized too small, the following symptoms are expected

ORA 04031_unable to allocate_bytes of shared memory
Library cache lock and pin contention (high library cache reloads), investigate in v$librarycache
Row cache enqueue contention (high row cache reloads), investigate in v$rowcache
Latch contention (Shared pool, library cache, row cache)

Links

Tanel Poder's tweet mentions that the paper Understanding Shared Pool Memory Structures from 2005 is still relevant.

Because the paper is a bit dated, it should mention that shared_pool_size should not be set on PDB level.