A High Level Comparison Between Oracle and SQL Server – Part II
When an Oracle instance is created, it is
nothing but a collection of some structured areas in the server’s memory and
some background processes spawned to interact with it. Oracle literature quite
extensively describes these memory structures and processes.
The area occupied in the memory by a running
Oracle instance is called the System Global Area (SGA). The size of the
SGA can be controlled by modifying an Oracle initialisation parameter.
Within the SGA, there are at least three distinct areas that are also created.
Database Buffer Cache:
This is where blocks of data are kept. Just like in SQL Server, users do not
directly access data from the database files: when data is read, the relevant
blocks are copied from the data files into this memory area. When data is
changed, it is updated here in the memory. The changed contents of the DB
buffer cache are written to the data files by a separate process.
Redo Log Buffer:
This area in the SGA continuously records the changes made to the data blocks
in the database buffer cache. Contents of the redo log buffer are written to
the online redo logs.
Within the SGA a chunk of memory is reserved for the Shared Pool. Shared pool
is the equivalent of SQL Server’s procedure cache. It is primarily used
for caching the most recently issued SQL statements against the database. The
components that make up shared pool consist of:
the dictionary cache stores information recently fetched from the data
dictionary by Oracle.
This area contains the recently executed SQL and PL/SQL statements and their
execution plans. The SQL area can be further divided into the shared and the
private areas: shared SQL area keeps statements that can be reused for multiple
users and the private area keeps the bind variables unique to each individual
connection. PL/SQL is Oracle’s own proprietary language that is an
enhancement over standard SQL. When PL/SQL program units like procedures and
functions are executing, their code is copied to the shared PL/SQL area in the
library cache. Apart from the codes and their execution plans, the library
cache also contains locks, latches and character set information.
A number of optional areas can also exist in
the SGA depending on what other components Oracle is running. The Java Pool
is used when Oracle uses Java application code. Oracle’s built-in backup and
recovery system RMAN (short for Recovery Manager) uses the Large
Pool. When Oracle’s advanced queuing mechanism is used, the Streams Pool
All these components in the memory are parts
of the SGA and they interact with the database through a number of processes.
We begin our tour of Oracle processes with an introduction of user and server
When a user or an application connects to an
Oracle database, a user process is started. In a two-tiered
architecture, the user process resides in the client workstation; in a three-tiered
application, the middle tier spawns the user process. Once the user process
connects to an Oracle listener service running in the database server,
the listener starts a server process for that user session. Just to make
things clear, an Oracle listener is the network component of Oracle that
handles oncoming database connections. Listener itself is a process and watches
for connection requests from clients. If the listener is not running, the
database cannot be connected to. Once the connection is made, the server
process spawned by the listener handles the user requests against the database.
In the most common scenario, each user’s connection spawns a separate,
dedicated server process. Oracle can also be configured to run in a mode where
a number of server processes are pre-created in a pool. User processes
connected to the database instance are allocated server processes from this
There can be a number of background
processes running in an Oracle instance at any one time. Of these, there are
five that are always executing.
The Database Writer process or DBWn
is responsible for writing the modified data blocks from the database buffer
cache into the data files. Oracle can run up to 20 instances of this process at
any one time to improve performance. The n in DBWn represents an individual
process: n can be anywhere between 0 to 9 and a to j.
While the dirty data blocks are written to
the data files by one or more DBWn processes, the Log Writer (LGWR)
process writes the contents of the redo log buffer into the online redo logs.
LGWR executes far more frequently than the database writer process because of
two reasons: the first is to ensure transaction information is hardened to disk
as quickly as possible so that the database can be recovered later if an
unexpected crash happens. The second reason is that redo information in the log
buffer is not the actual changed data, but the change that happened to the data
- and thus has a much smaller footprint in terms of size.
The Checkpoint process (CKPT)
is responsible for periodically synchronising the contents of the SGA and the
database. When a checkpoint runs, it invokes the DBWn process to write the
dirty data blocks to the data files, flushes the contents of the redo log
buffer to the online redo logs and then updates the headers of the data files
and the database control file.
Sometimes, if a user session against the
database crashes or terminate unexpectedly, the Process Monitor (PMON)
process will take care of any loose ends remaining at the back-end side. This
will include releasing any locks held by the terminated user session,
reclaiming any resources allocated and ensuring the process ID is removed from
the list of active processes. PMON is like an UNIX daemon: it wakes up
periodically to see if any house-cleaning needs to be done. It can initiate
other processes if necessary and other background processes can invoke it as
The System Monitor or SMON is
invoked by Oracle automatically when an instance starts after a server crash.
SMON performs crash recovery using the online redo log files.
One of the optional processes that may be
running in an Oracle instance is the Archiver (ARCn) process.
Once again the “n” represents an individual instance of the process. ARCn runs
when the database is running in the archive log mode. The archiver backs
up the contents of a full redo log into a disk file known as the archive log
before Oracle overwrites it. That way, the contents of the online redo logs are
The following diagram shows a very simplified
view of the Oracle instance architecture.
Finally, you want to see the components of
the System Global Area by running the following query:
To see the total amount of memory allocated
to the Oracle instance, run the following query:
see what Oracle processes are running in the instance, you can run a query like
Microsoft SQL Server also maintains internal
memory structures and runs background processes when an instance is up and
running. Unlike Oracle though, the inner workings and architecture is not
widely published in SQL Server literature.