What is the shared pool?
Figure 1: Oracle 7 and Oracle 8 Shared
Pool Structures
As you can see from examining the structures
pictured in Figure 1, the shared pool is separated into many
substructures. The substructures of the shared pool fall into two
broad areas, the fixed size areas that for a given database at a
given point in time stay relatively constant in size and the
variable size areas that grow and shrink according to user and
program requirements.
In Figure 1 the areas inside the library caches
substructure are variable in size while those outside the library
caches (with the exception of the request and response queues used
with MTS) stay relatively fixed in size. The sizes are determined
based on an Oracle internal algorithm that ratios out the fixed
areas based on overall shared pool size, a few of the intialization
parameters and empirical determinations from previous versions. In
early versions of Oracle (notably 6.2 and lower versions) the
dictionary caches could be sized individually allowing a finer
control of this aspect of the shared pool. With Oracle 7 the
internal algorithm for sizing the data dictionary caches took
control from the Remote DBA.
The shared pool is used for objects that can be
shared among all users such as table definitions, reusable SQL
(although non-reusable SQL is also stored there), PL/SQL packages,
procedures and functions. Cursor information is also stored in the
shared pool. At a minimum the shared pool must be sized to
accommodate the needs of the fixed areas plus a small amount of
memory reserved for use in parsing SQL and PL/SQL statements or
ORA-07445 errors will result.
