What Comprises the Oracle Shared Pool

The shared pool is an area of shared memory that holds information related to executing SQL statements and PL/SQL code. It is built from three major structures: the Library Cache, the Data Dictionary Cache, and the shared pool's own Control Structures. The diagram below shows all three together, including a detail easy to miss: the Library Cache has its own internal Control Structures, separate from the shared-pool-level Control Structures sitting alongside the Data Dictionary Cache.

The shared pool contains three major structures: the Library Cache, the Data Dictionary Cache, and its own set of Control Structures. The whole pool is sized by the SHARED_POOL_SIZE initialization parameter, a dynamic parameter that can be resized on the fly as long as the total SGA size stays under SGA_MAX_SIZE or SGA_TARGET, whichever governs your instance's memory management approach.

The Library Cache holds information about the SQL and PL/SQL statements run against the database, and because it is shared by every session connected to the instance, many different users can potentially reuse the exact same cached SQL statement rather than each paying the cost of processing it from scratch. It breaks down into three pieces:

• Shared SQL Area: alongside the SQL statement text itself, Oracle stores its parse tree and execution plan here. The second time an identical statement runs, by the same user or a completely different one, that parse tree and execution plan are already computed, meaningfully improving execution time for the query or DML statement.
• PL/SQL Code: packages, procedures, and functions are cached the same way. Every user of a given PL/SQL program unit shares a single cached copy rather than each session compiling its own.
• Control Structures (Library Cache): internal bookkeeping the Library Cache needs to manage itself, primarily locks and library cache handles that coordinate concurrent access to cached SQL and PL/SQL.

If the Library Cache is sized too small, execution plans and parse trees get flushed out well before you'd want them to, forcing frequent, expensive reloads of statements that should have stayed cached. You can check reload and invalidation activity directly with:

SELECT namespace, gets, gethits, pins, reloads, invalidations FROM V$LIBRARYCACHE;

A namespace with a high reload count relative to its pin count is usually a sign the Library Cache, or the application's SQL itself, needs attention.

The data dictionary is a collection of database tables, owned primarily by the SYS schema with supporting views and synonyms in SYSTEM, containing metadata about the database itself: its structures, and the privileges and roles of its users. The Data Dictionary Cache holds a subset of columns from those dictionary tables, cached after first being read into the buffer cache, because they get consulted constantly while processing ordinary user queries and DML.
If the Data Dictionary Cache is sized too small, requests for dictionary information cause extra I/O that wouldn't otherwise be necessary. These I/O-bound dictionary requests are called recursive calls, and correctly sizing the Data Dictionary Cache is exactly how you avoid generating them unnecessarily. You can check dictionary cache hit ratios with:

SELECT parameter, gets, getmisses FROM V$ROWCACHE;

and confirm overall recursive call volume instance-wide with:

SELECT name, value FROM V$SYSSTAT WHERE name IN ('recursive calls', 'recursive cpu usage');

Separate from the smaller Control Structures area inside the Library Cache, the shared pool maintains its own Control Structures at the top level. This area holds character set conversion memory, used when client and database character sets differ, and network security attributes tied to session authentication and encryption. Unlike the Library Cache and Data Dictionary Cache, there's no dedicated initialization parameter for sizing this area independently; it draws from the same overall SHARED_POOL_SIZE allocation as everything else in the pool.

The Shared SQL Area inside the Library Cache deserves a closer look, since it's the part of the shared pool you'll interact with most directly when tuning SQL performance. When you send a SQL statement to the database, Oracle has to determine how to actually execute it: which tables are involved, which indexes to use, if any, what join order makes sense, and so on. This process is called parsing, and it typically involves a number of recursive queries against the data dictionary itself, tying the Shared SQL Area's performance directly back to how well the Data Dictionary Cache above is sized. A statement that has to be hard-parsed every time, rather than reusing a cached parse from the Shared SQL Area, pays this dictionary lookup cost repeatedly instead of once.