UNIT 2

LESSON 8:
ORACLE MEMORY STRUCTURE

This lesson provides an insight into The database buffer cache caches the database data, holding

ORACLE DATABASE ADMINISTRATION

• Overview of Oracle Memory Structures blocks from the data files that have been recently read. The
database buffer cache is shared among all the users connected to
• System Global Area
the database. There are three types of buffers:
• Programme Global Area
• Dirty buffers: Dirty buffers need to be written to the data
• Software Code Area file. The data in these buffers has changed and has not yet
Introduction been written to the disk.
The memory structures of any database are used to cache • Free buffers: Free buffers are the buffer blocks that do not
application data, data dictionary information which contains contain any data or are free to be over-written. When Oracle
metadata etc, SQL commands, PL/SQL and Java program reads data from disk, free buffers are used to hold this data.
units, transaction information, data required for execution of • Pinned buffers: Pinned buffers are the buffers that are
individual database requests, and other control information. currently being accessed or explicitly retained for future use.
Memory structures are allocated to the Oracle instance when the For example, the KEEP buffer pool.
instance is started. The two major memory structures are
To manage the buffer cache, two lists are maintained by Oracle:
known as the System Global Area (also called the Shared
Global Area) and the Program Global Area or the Process 1. The write list contains the buffers that are modified and
Global Area. need to be written to the disk (the dirty buffers). It is also
called as dirty buffer list.
2. The least recently used (LRU) list contains free buffers,
pinned buffers, and the dirty buffers that have not yet been
moved to the write list. Consider the LRU list as a queue of
blocks, where the most recently accessed blocks are always in
the front(known as the most recently used , or MRU, end
of the list; the other end, where the least recently accessed
blocks are, is the LRU end). However the least used blocks
are thrown out of the list when new blocks are accessed and
are added to the list.
When an Oracle process accesses a buffer, it is moved to MRU
end of the list. So the most frequently accessed data is available
in the buffers. When new data buffers are moved to the LRU
list, they are copied to the MRU end of the list, pushing out the
buffers from the LRU end. An exception to this occurs when a
full table scan is done and the blocks from a full table scan are
written to the LRU end of the list. When an Oracle process
requests data, it searches the data in the buffer cache, and if it
finds data, the result is a cached hit. If it cannot find the data,
the result is a cache miss, and data then needs to be copied from
disk to the buffer.
Figure illustrates the various memory structures in Oracle. Before reading a data into cached, the process must first find a
System Global Area free buffer. The server process on behalf of the process
The System Global Area is a shared memory area, the searched either until it finds a free buffer or until it has searched
information of which is shared by all the users of the database. the threshold limit of buffers. If the server process finds a dirty
Oracle instance is constituted by the SGA and the background buffer as it searches the LRU list, it moves that buffer to the
processes. Oracle allocates memory of the SGA when an Oracle write list and continues to search. When the process finds a free
instance is started and de-allocates it when the instance is shut buffer, it reads the data block from the disk into the buffer and
down. The information stored in the SGA is divided into moves the buffer to the MRU end of the LRU list. If an Oracle
multiple memory structures that are allocated fixed space when server process searches the threshold limit of buffer without
the instance is started. finding a free buffer, the process stops searching and signals the
The various components of SGA are as follows: DBWn background process to write some of the dirty buffers
to disk.
1. Database buffer cache

© Copy Right: Rai University

3E.672/3B.581 21
 Oracle8i lets you divide the buffer pool into three areas: structures, its privileges, and its users. Oracle accesses the data
ORACLE DATABASE ADMINISTRATION

• The KEEP buffer pool retains the data blocks in memory; dictionary frequently during the parsing of SQL statements.
they are not aged out. The data dictionary cache holds the most recently used database
dictionary information. The data dictionary cache is also known
• The RECYCLE buffer pool removes the buffers from
as the row cache because it holds data as rows instead of buffers
memory as soon as they are not needed.
(which hold entire blocks of data).
• The DEFAULT buffer pool contains the blocks that are not
assigned to the other pools. 4. Large pool
The Large pool is a voluntary area in the SGA that can be
2. Redo log buffer configured by the database administrator to provide large
The redo log buffer is a circular buffer in the SGA. It holds memory allocations for specific database operations such as an
information about the changes, which are made to the database Oracle backup or restore. The large pool allows Oracle to request
data. Such changes are known as redo entries or change vectors large memory allocations from a separate pool to prevent
and are used to redo the changes in case of any failure. Changes contention from other applications for the same memory. The
are made to the database through any of these commands: large pool does not have an LRU list. The parameter
INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP. LARGE_POOL_SIZE specifies the size of the large pool.
The parameter LOG_BUFFER determines the size of the redo
log buffer. Programme Global Area
This is another memory structure of Oracle. The Program
3. Shared Pool Global Area (PGA) is the area in the memory that contains the
This portion of the SGA holds information like SQL, PL/SQL data and process information for one process. This area is non-
procedures and packages, the data dictionary, character set shared memory. The PGA size is fixed and is dependent on the
information, locks, security attributes, and so on. The parameter operating system. The contents of the PGA vary depending on
SHARED_POOL_SIZE is used to determine the size of the the server configuration.
shared pool.
• For a dedicated server configuration, the PGA holds stack
The shared pool further consists of the library cache and the space and sessions information.
dictionary cache.
• For multithreaded configurations (where user connections
• Library Cache go through a dispatcher, a smaller number of server
The library cache contains the shared and private SQL areas, PL/ processes are required as they can be shared by multiple user
SQL procedures and packages and control structures such as processes), the PGA contains the stack space information.
locks and library cache handles. The shared SQL area maintains In that case the session information is in the SGA.
recently executed SQL commands and their execution plans. The memory allocated to hold variables, arrays, and other
Each SQL statement executed is divided into a shared area and a information that belongs to the session is called stake space.
private SQL area by Oracle. PGA is allocated when the process is completed. Unlike the
When two users are executing the same SQL, the information SGA that is shared by several processes, the PGA provides sort
in the shared SQL area is used for both. The shared SQL area space, session information, stack space, and cursor information
contains the parse tree and execution plan, whereas the private for a single server process.
SQL area contains values for the bind variables (persistent area) Sort area
and runtime buffers (runtime area). Oracle creates the runtime The memory area that Oracle uses to sort data is known as the
area as the first step of an execute request. For INSERT, sort area, which uses memory from the PGA for a dedicated
UPDATE, and DELETE statements, Oracle frees the runtime server connection. For multithreaded server configurations, the
area after the statement has been executed. For queries, Oracle sort area is allocated from the SGA. Sort area size can grow
frees the runtime area only after all rows have been fetched or depending on the need. The parameter
the query has been canceled. SORT_AREA_RETAINED_SIZE determines the size to
PL/SQL program units are processed the same way as SQL which the sort area is reduced after the sort operation. The
statements by Oracle. When a PL/SQL program unit is memory released from the sort area is kept with the server
executed, the code is moved to the shared PL/SQL area while process; it is not released to the operating system.
the individual SQL commands within the program unit are Software Code Area
moved to the shared SQL area. Again, the shared program units This is another portion of the memory. Software code areas are
are maintained in memory with an LRU algorithm. the portions of memory that are used to store the code that is
The third area of the library cache is maintained for internal use being executed. Software code areas are mostly static in size and
by the instance. Various locks, latched, and other control are dependent on the operating system. These areas are read-
structures reside here and are freely accessed by any server only and can be shared (if the operating system allows), so
processes requiring this information. multiple copies of the same code are not kept in memory. Some
• Data dictionary cache Oracle tools and utilities (such as SQL * Forms and SQL *
The data dictionary is nothing, but a collection of database Plus) can be installed shared, but some cannot. Multiple
tables and views containing metadata about the database, its instances of Oracle can use the same Oracle code area with
different databases if running on the same computer.

© Copy Right: Rai University

22 3E.672/3B.581