Oracle Architecture Part 4

[embedplusvideo height=”328″ width=”584″ standard=”″ vars=”ytid=nNGLyWIPPTQ&width=584&height=328&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep8443″ /]

Watch the earlier parts first:

Part 1

Part 2

Part 3



Oracle Processes



Database Writer

  • Writes from the SGA to the data files
  • Does deffered writes
  • like a hotel room clerk
  • kicks blocks out to disk when new data needs free blocks



Log Writer

  • writes info to the current group of redo logs


System monitor

  • very important process
  • performs instance recovery
  • cleans up after sorts



Process monitor

  • Monitors Processes
  • detects – deadlock detected for example



Checkpoint process

one big reason for checkpoints is that commonly accessed
data may never get written out to disk. A checkpoint forces
this to get written.

Oracle Instance

– is the SGA and background processes


Oracle database and the Oracle instance combined is called the Oracle Server.

You can have multiple instances associated with the same database, this is called RAC.

Dedicated server process connects through the PGA.

Oracle Checkpoints

When the data in a oracle database needs to be changed, it has to be done quickly. Accessing the data files on disk is slow and so, all changes in the Oracle database are carried out in the buffer cache and are only written to files at a later point by the DBWR.

The buffer is of finite size and so will fill up after a while. More data will need to be written in to the buffer meaning that old data will need to be written to disk and cleared out. A Least recently Used (LRU) algorithm is used to determine what data to write to disk and remove from the buffer cache.

Popular data will constantly be moving to the top of the queue and may never reach the bottom of the LRU queue where it would be written to disk.

This would be a problem and so Oracle uses something called checkpoints to ensure that all data is eventually written out to disk, regardless of how often it is accessed in memory.

Checkpoints are signaled by either the LGWR (Log Writer) process or the CKPT (checkpoint) background process.

There are two types of checkpoints:

  • Fast Checkpoints
  • Normal Checkpoints

Normal Checkpoints.

  • The DBWR will write a few extra buffers each time it runs.
  • Normal checkpoints will take much longer to complete but have little effect on system performance while they run.

Fast Checkpoints.

  • At each checkpoint, the DBWR writes a large number of buffers.
  • This type of checkpoint completes much quicker, but has a large impact on system while it is running
  • Fast checkpoints are more efficient overall in terms of I/O.

A checkpoint is automatically triggered on a log switch.

You can reduce the recovery time of the database by generating more frequent checkpoints.

How does Oracle Write to the Database

The Users server process is the process that handles the users requests to the oracle database.

When the user requests a add, update or delete of data in the database, the server process modifies the data from the buffer cache, not the actual data files.

The DBWR (Database Writer) process is the only process that writes to the data files.

Here is the process by which data is modified in the Oracle database:

If the operation is an Insert:

Space found in a block in buffer cache.
Data inserted into that block buffer.

If the operation is an Update or a Delete:

The data is read, either from the buffer cache or the data files and if from the datafiles, it is placed in the buffer.
The data is deleted or modified in the buffer cache.
A copy of the origional data written to the UNDO tablespace in case it is needed for  a rollback or for read consistency.

Once the data  is modified in the Buffer Cache, a log file is written to the Redo log.
Once the Redo log is written out, the change is considered complete.

Later…the DBWR writes the changed blocks to the datafiles.
The DBWR is the only process that can write to datafiles but the Server Processes can read form them.