Donald K. Burleson
Oracle Shared Pool
The shared pool is the most important area of the SGA, except for the data
buffer caches. There are a number of sub-areas within the SGA, each with
its own important purpose. Unfortunately, all of the sub-areas are
controlled by the single shared_pool_size parameter. A shortage
of shared pool RAM may result in high library cache reloads, high row
cache reloads, and shared pool latch contention. You may also see the
error: "ORA-04031: Out of shared pool memory".
details on Oracle shared pool memory, see my master book "Oracle
Tuning: The Definitive Reference".
Since it is not possible to dedicate separate regions of memory for the
shared pool components, the shared pool is usually the second-largest SGA
memory area (depending on the size of the db_cache_size parameter). The
shared pool contains RAM memory regions that serve the following purposes:
• Library cache – The library cache contains the current SQL execution plan
information. It also holds stored procedures and trigger code.
Dictionary cache - The dictionary cache stores environmental information,
which includes referential integrity, table definitions, indexing
information, and other metadata stored within Oracle's internal tables.
• Session information – Systems that use SQL*Net version 2 with a
multi-threaded server need this area to store session information.
Beginning with Oracle, the v$session view contains information related to
Also, see my
other notes on the Oracle shared pool:
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA