Question: What is the Oracle
shared_pool_size parameter and what is contained in this shared pool?
Answer: the Shared Pool is a
RAM area within the RAM heap that is created at startup time, a component of the
System Global Area (the SGA). 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".
The Oracle shared pool contains Oracle's library cache,
which is responsible for collecting, parsing, interpreting, and executing all of
the SQL statements that go against the Oracle database. Hence, the shared pool
is a key component, so it's necessary for the Oracle database administrator to
check for shared pool contention.
The shared pool is like a buffer for SQL statements.
Oracle's parsing algorithm ensures that identical SQL statements do not have to
be parsed each time they're executed. The shared pool is used to store SQL
statements, and it includes the following components:
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 Oracle*Net users.
The following table lists the different areas stored in
the shared pool and their purpose:
* Shared SQL Area - The shared SQL area stores each SQL
statement executed in the database. This area allows SQL execution plans to be
reused by many users.
* Private SQL Area - Private SQL areas are non-shared
memory areas assigned to unique user sessions.
* PL/SQL Area - Used to hold parsed and compiled PL/SQL
program units, allowing the execution plans to be shared by many users.
* Control Structures - Common control structure
information, for example, lock information
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. We will discuss the data dictionary
later in this book.
As with the database buffer cache, the shared pool is
critical to performance. Later in this book we will discuss the concept of
Oracle SQL statement reuse. Reusability is a concept that is very important when
it comes to performance relating to the shared pool!
Thus far we have discussed Oracle?s in-memory storage of
data, SQL and control structures but there is one other very important SGA
structure to be mentioned, the redo log buffer.
Adjusting the Oracle10g Database Shared Pool
Many DBAs know that there are several queries for determining
when the Oracle shared pool is too small. The library cache miss
ratio tells the DBA whether to add space to the shared pool, and
it represents the ratio of the sum of library cache reloads to the
sum of pins.
In general, if the library cache ratio is over 1, you should
consider adding to the shared_pool_size. Library cache misses
occur during the parsing and preparation of the execution plans
for SQL statements.
The compilation of a SQL statement consists of two phases: the
parse phase and the execute phase. When the time comes to parse an
SQL statement, Oracle checks to see if the parsed representation
of the statement already exists in the library cache. If not,
Oracle will allocate a shared SQL area within the library cache
and then parse the SQL statement. At execution time, Oracle checks
to see if a parsed representation of the SQL statement already
exists in the library cache. If not, Oracle will reparse and
execute the statement.
The following STATSPACK script will compute the library cache
miss ratio. Note that the script sums all of the values for the
individual components within the library cache and provides an
instance-wide view of the health of the library cache.
Here is the output. This report can easily be customized to
alert the DBA when there are excessive executions or library cache
misses.
Cache Misses Library Cache
Yr. Mo Dy Hr. execs While Executing Miss Ratio
---------------- ---------- --------------- ---------------
2001-12-11 10 10,338 3 .00029
2001-12-12 10 182,477 134 .00073
2001-12-14 10 190,707 202 .00106
2001-12-16 10 2,803 11 .00392
Once this report identifies a time period where there may be a
problem, STATSPACK provides the ability to run detailed reports to
show the behavior of the objects within the library cache.
In the preceding example, you see a clear RAM shortage in the
shared pool between 10:00 A.M. and 11:00 A.M. each day. In this
case, you could dynamically reconfigure the shared pool with
additional RAM memory from the db_cache_size during this period.
However, when Oracle Database 10g has the sga_target parameter
set, it will automatically re-allocate space as needed, up to the
value of sga_target, by dynamically removing space for other
components who aren't experiencing stress and reallocating to the
stressed shared pool.
The Oracle Database 10g workload manager promises to do this
type of monitoring and anticipates upcoming RAM shortages,
allocating additional RAM just in time for the anticipated event.
Let?s take a look at how Oracle Database 10g monitors the shared
pool.
Here is a script to
measure object usage inside the shared pool:
and include the URL for the page.
