Inside the Shared Pool
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:
* The library cache
* The dictionary cache
* Control structures
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.
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
publisher
here.
Oracle DBA
Brian Mullin
has donated these scripts to measure object usage inside the shared pool:
