René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

Shared pool [Oracle]

The shared pool is the part of the SGA where (among others) the following things are stored:

Shared pool latch

The shared pool latch is used when memory is allocated or freed in the shared pool.

Library cache latch

Similar to the shared pool latch, this latch protects operations within the library cache.


Flushing the shared pool

The shared pool can be flushed with alter system flush shared_pool.

Allocation of memory

Memory in the shared pool is managed and freed in a LRU fashion, that is, Memory that isn't used for long gets aged out first. This is in contrast to the large pool, where memory is managed in a heap (using allocate and free).


Up until 9g, the shared pool was always allocated in one large heap. From 10i, there's the possibility to split split the shared pool into multiple seperate subpools (or areas). This behaviour is controlled by the _kghdsidx_count parameter.

Pinning objects

Examining the shared pool

select    name, bytes/1024/1024 "MB"
  from    v$sgastat
 where    pool = 'shared pool'
 order by bytes desc;