Step 5 - Instance Memory Structure
Learn Oracle - Instance Memory Structure
This section briefly describes the memory structures of an instance. The size of these
structures affects the performance of the Oracle database server and is controlled by
initialization parameters. These initialization parameters can be categorized as
When a database is created with DBCA, the memory parameters are automatically set
to optimal values based on your specification of the database workload. However, as
your database usage expands, Oracle DBA might find it necessary to alter the settings of the
Oracle provides alerts and advisors to identify memory sizing problems and to help
Oracle DBA determine appropriate values for memory parameters.
The System Global Area (SGA)
The SGA is a shared memory area that contains data and control information for the
instance. Multiple users can share data within this memory area (controlled by Oracle)
and information stored in the SGA can avoid repeated access from physical disk, a
time consuming operation.
For optimal performance, the SGA should be large enough to avoid frequent disk
reads and writes.
The SGA has several subcomponents as listed :
Before any data stored in the database can be queried or modified, it must
be read from disk and stored in memory. The buffer cache is the
component of the SGA that acts as the buffer to store any data being
queried or modified. All user processes connected to the database share
access to the buffer cache.
The shared pool caches information that can be shared among users.
SQL statements are cached so that they can be reused
Information from the data dictionary such as user account data, table
and index descriptions, and privileges is cached for quick access and
Stored procedures, which are executable code that is stored in the
database, can be cached for faster access
Redo Log Buffer
This buffer improves performance by caching redo information (used for
instance recovery) until it can be written at once and at a more opportune
time to the physical redo log files that are stored on disk.
This is an optional area that is used for buffering large I/O requests for
various server processes.
The Java pool memory is used for all session-specific Java code and data
within the Java Virtual Machine (JVM)
Program Global Area (PGA)
A program global area (PGA) is a memory area used by a single Oracle server process.
A server process is a process that services a client’s requests. Each server process has
its own private PGA area that is a nonshared area of memory created by Oracle when
a server process is started.
The PGA is used to process SQL statements and to hold logon and other session
The amount of PGA memory used and its content depends on the instance
configuration, that is, whether the instance is running in dedicated server or shared