Shared Pool

Shared Pool contains two very important memory structures :

a) Library Cache

b) Data Dictionary Cache

c) Control Structure

Shared Pool can be sized by by using SHARED_POOL_SIZE initialization parameter but it can not be greater than SGA_MAX_SIZE.

a) Library Cache :

Stores information about the most recently used SQL and PL/SQL statements

The Library Cache is divided into Namespaces (or Libraries). These can be found through v$librarycache

SQL> select namespace from v$librarycache;

NAMESPACE
---------------
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE
JAVA SOURCE
JAVA RESOURCE
JAVA DATA

11 rows selected.

consists of two structures :

1) Shared SQL Area:

    stores and share the execution plan and parse tree for statement is run, to take the advantage of already prepared execution plans but to take the advantage of already created execution plan in Shared Sql Area the text,schema and bind variables must be exactly the same.

2) Share PL/SQL Area:

 Most recently executed PL/SQL statements.

b) Data Dictionary Cache :

The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.

c) Control Structures - Common control structure information, for example, lock information