DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.

com

Oracle Memory Architecture

Oracle Database Management System has two main components, those are: -
1. Oracle Database
2. Oracle Instance
Oracle Database Consist of: -

- Datafile
- Control-file
- Online Redo Logfile
- Archive log
- INIT file - PFILE / SPFILE
- Password file

Oracle Instance: -
When a database is started, Oracle allocates a memory area, called the System Global Area
(SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle
processes is called an Oracle instance.
Simply, Instance consists of –

i. Background Processes
ii. Memory Architecture
- Shared Memory (SGA)
- Non-shared Memory (PGA)

Total memory to oracle = SGA + PGA + Background Process.

System Global Area (SGA): -

The SGA is a group of shared memory structures, known as SGA components, which contain
data and control information for one Oracle Database instance.

The SGA is shared by all server processes and background processes.

The purpose of SGA is to speed up query performance and to enable a high amount of
concurrent database activity. Because processing in memory is much faster than disk I/O.

Program Global Area (PGA): -

A PGA is a memory region that contains data and control information for a server process.

DBA Trainer - Online Oracle DBA Training InstitutePage 1

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

It is nonshared memory created by Oracle Database when a server process is started.

User Global Area (UGA):-

This is memory associated with your session. It is located either in the SGA or the PGA,
depending whether you are connected to the database using a shared server (it will be in
the SGA), or a dedicated server (it will be in the PGA).

SGA in Detail: -

SGA consists of several memory structures as below: -

1. Database Buffer Cache
2. Shared Pool( Library Cache, Data Dictionary Cache , Server Result Cache , Reserved
Pool )
3. Redo Log Buffer
4. Java Pool
5. Stream Pool
6. Large Pool

1. Database Buffer Cache

The block buffer cache is where Oracle stores database blocks before writing them to
disk and after reading them in from disk.

All users concurrently connected to the instance share access to the database buffer
cache.

2. Shared Pool

The Shared Pool caches various types of program data.

DBA Trainer - Online Oracle DBA Training InstitutePage 2

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

For example, the shared pool stores parsed SQL, PL/SQL code, system parameters,
and data dictionary information.

The shared pool is involved in almost every operation that occurs in the database.

For example, if a user executes a SQL statement, then Oracle Database accesses the
shared pool.

The shared pool is divided into several sub-components as below: -

A. Library Cache
B. Data Dictionary Cache
C. Server Result Cache
D. Reserved Pool

In Detail: -

Library Cache

Library Cache is a shared pool memory structure that stores executable SQL and
PL/SQL code.

When a SQL statement is executed, the database attempts to reuse previously

executed code. If a parsed representation of a SQL statement exists in the library
cache and can be shared, then the database reuses the code, known as a soft
parse or a library cache hit. Otherwise, the database must build a new executable
version of the application code, known as a hard parse or a library cache miss.

Data Dictionary Cache

The data dictionary is a collection of database tables and views containing reference
information about the database, its structures, and its users.

This cache holds information about database objects. The cache is also known as
the row cache because it holds data as rows instead of buffers.

Server Result Cache

the server result cache holds result sets and not data blocks.

The server result cache contains the SQL query result cache and PL/SQL function
result cache.

DBA Trainer - Online Oracle DBA Training InstitutePage 3

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

For example, suppose an application runs the same SELECT statement repeatedly. If
the results are cached, then the database returns them immediately. In this way, the
database avoids the expensive operation of rereading blocks and recomputing
results.

Reserved Pool

The reserved pool is a memory area in the shared pool that Oracle Database can use
to allocate large contiguous chunks of memory.

Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared
pool that are larger than 5 KB. To allow these allocations to occur most efficiently,
the database segregates a small amount of the shared pool for the reserved pool.

3. Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that stores redo entries describing
changes made to the database.

Redo entries contain the information necessary to reconstruct, or redo, changes

made to the database by DML or DDL operations.

Database recovery applies redo entries to data files to reconstruct lost changes.

4. Java Pool

The Java pool is an area of memory that stores all session-specific Java code and
data within the Java Virtual Machine (JVM).

5. Stream Pool

The Streams pool stores buffered queue messages and provide memory for Oracle
Streams capture processes and apply processes.

The Streams pool is used exclusively by Oracle Streams.

6. Large Pool

It is used when you are performing activity like Backup / Restore / Recovery or any
kind of parallel query execution (distributed environment).

DBA Trainer - Online Oracle DBA Training InstitutePage 4

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

PGA in Detail: -

PGA is the memory reserved for each user process connecting to an Oracle Database and is
allocated when a process is created and deallocated when a process is terminated.

PGA consists of several memory structures as below: -

Private SQL Area:
It contains Persistent Area which contains bind information and is freed only when the
cursor is closed and Run time Area which is created as the first step of an execute request.
This area is freed only when the statement has been executed.
Session Memory:

Consists of memory allocated to hold a session’s variable and other info related to the
session.

SQL Work Areas:

Used for memory intensive operations such as: Sort, Hash-join, Bitmap merge, Bitmap
Create.

DBA Trainer - Online Oracle DBA Training InstitutePage 5

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

DBA Trainer - Online Oracle DBA Training InstitutePage 6

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

Background Process in Detail: -

Two types of Processes in Oracle Database System: -

1. User Processes
2. Oracle Processes
Let’s Discuss in Detail: -
User / Client Processes: -
When a user runs an application programme an Oracle tool, Oracle creates a user process to
run the user's application.
Connections and Sessions: -
A connection is a communication pathway between a user process and an Oracle instance.
A session is a specific connection of a user to an Oracle instance through a user process.
For example, when a user starts SQL*Plus, the user must provide a valid username and
password, and then a session is established for that user.
Oracle Processes: -
Two types of Oracle Processes: -

DBA Trainer - Online Oracle DBA Training InstitutePage 7

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

A. Server Processes
B. Background Processes
Server Processes: -
Oracle creates server processes to handle the requests of user processes connected to the
instance.
Server processes created on behalf of each user's application can perform one or more of
the following:
 Parse and run SQL statements issued through the application
 Read necessary data blocks from data files on disk into the shared database buffers
of the SGA, if the blocks are not already present in the SGA
 Return results to user process
Background Processes: -
Oracle Instance can have many background processes as below: -
1. Database Writer Process ( DBWn )
2. Log Writer Process ( LGWR )
3. Checkpoint Process ( CKPT )
4. System Monitor Process ( SMON )
5. Process Monitor Process ( PMON )
6. Recover Process ( RECO )
7. Archiver Processes ( ARCn )
Background processes are created automatically when an instance is started.
Database Writer Process ( DBWn ) :-
The database writer process (DBWn) writes the contents of buffers to datafiles.
The DBWn processes are responsible for writing modified (dirty) buffers in the database
buffer cache to disk.
Although one database writer process (DBW0) is adequate for most systems, you can
configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve
write performance if your system modifies data heavily.
Log Writer Process (LGWR):-
The log writer process (LGWR) is responsible for redo log buffer management—writing the
redo log buffer to a redo log file on disk.
LGWR writes all redo entries that have been copied into the buffer since the last time it
wrote.
LGWR writes when following condition meet: -
 A commit record when a user process commits a transaction

DBA Trainer - Online Oracle DBA Training InstitutePage 8

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

 Every three seconds

 When the redo log buffer is one-third full
 When a DBWn process writes modified buffer to disk , If necessary

Checkpoint Process (CKPT):-

Checkpoint is a background process which triggers the checkpoint event, to synchronize all
database files with the checkpoint information.

It ensures data consistency and faster database recovery in case of a crash.

When checkpoint occurred it will invoke the DBWn and updates the SCN( System change
Number ) block of the all datafiles and the control file with the current SCN. This is done by
LGWR.

Checkpoint event can be occurred in following conditions:

 Whenever database buffer cache filled up.

 Log switch occurred
 Whenever manual log switch is done.
SQL> ALTER SYSTEM SWITCH LOGFILE;
 Manual checkpoint.
SQL> ALTER SYSTEM CHECKPOINT;
 Graceful shutdown of the database.
 Whenever BEGIN BACKUP command is issued.

System Monitor Process (SMON):-

The system monitor process (SMON) performs recovery (instance recovery), if necessary, at
instance startup.

Process Monitor Process (PMON)

The process monitor (PMON) performs process recovery when a user process fails.
PMON is responsible for cleaning up the database buffer cache and freeing resources that
the user process was using.
Recoverer Process (RECO)
The recoverer process (RECO) is a background process used with the distributed database
configuration that automatically resolves failures involving distributed transactions.
The RECO process of a node automatically connects to other databases involved in an in-
doubt distributed transaction.

DBA Trainer - Online Oracle DBA Training InstitutePage 9

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

Job Queue Processes

Job queue processes are used for batch processing.
They run user jobs.
They can be viewed as a scheduler service that can be used to schedule jobs as PL/SQL
statements or procedures on an Oracle instance.
Job queue processes are managed dynamically.
Dynamic job queue processes can run many jobs concurrently at a given interval.

1. The coordinator process, named CJQ0, periodically selects jobs that need to be run
from the system JOB$ table. New jobs selected are ordered by time.

2. The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to run
the jobs.

3. The job queue process runs one of the jobs that was selected by the CJQ process for
execution. The processes run one job at a time.

Archival Processes ( ARCn ) :-

The archiver process (ARCn) copies redo log files to a designated storage device after a log
switch has occurred. ARCn processes are present only when the database is
in ARCHIVELOG mode.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process
starts a new ARCn process whenever the current number of ARCn processes is insufficient to
handle the workload.

DBA Trainer - Online Oracle DBA Training InstitutePage 10

DBA Trainer Oracle DBA - Money Architecture www.dbatrainer.com

DBA Trainer - Online Oracle DBA Training InstitutePage 11