Oracle Architecture

User Process - A user process connects with a server process toestablish a session.

Server Process : serves the requests of the user process.

Oracle can be said to be composed of 2 major components :

1. Instance =SGA ( System Global Area) + Background Processes

2. Database - All the Files ( C - D - R) - This is the physical Database

C = Control File , D= Datafile , R= Redo log file

SGA (System Global Area)
------------------------------------------------------------
Major Components :

SGA = Database Buffer Cache + Shared Pool + Redo Buffer

Shared Pool = Library Cache + Data Dictionary Cache

Shared Pool

Library Cache


The compiled version of the statement (Parse Tree)The Execution Plan - tells how to run the statement, determined by theoptimizer.

Data dictionary cache: Used during the Parse phase to resolve objectnames and validate access privileges.

Table and column definitionsUser names, Passwords and Privileges

-Database Buffer cache: Holds the most recently used data.The Least Recently Used (LRU) algorithm is used to age out blocks ofdata in the Buffer Cache. Each buffer in the buffer cache is equal tothe size of the specified data block size.

DB_BLOCK_BUFFERS -parameter sets the number of buffers available.

DB_BLOCKSIZE - parameter sets the size of the data block

-Redo Log Buffer: Registers changes made to the database via theinstance. Size is defined by LOG_BUFFER in the parameter file.

Stores records of changes: Block that changed, the new value, andlocation of the change.The buffer is reused after the redo entries are recorded in the redo logfiles.

PGA (Program Global Area)Used by one Process only and contains the following
Session Information Sort AreaCursor StateStack SpaceDBWR (Database Writer)Writes changed data to the database file when one of the followingconditions are met:Dirty Buffers reach a threshold valueNo free blocks are availableA timeout occurs.

A DBWR checkpoint takes placeLGWR (Log Writer)Writes changes registered in the redo log buffer to the database filewhen one of the following conditions are met:

When the redo log buffer is 1/3 full

A timeout occurs (about every 3 seconds)Before DBWR writes modified blocks to the data files
A transaction COMMITS

SMON (System Monitor)Checks for consistency and initiates recovery.

PMON (Process Monitor)Cleans up resources after a process fails. Watches after the serverprocess

CKPT (Checkpoint Process)Updates the database status information after changes are permanentlywritten to the database file from the buffer cache.

Database Files
-Data files
-Redo log files
-Control files
-Parameter file
-Password file
-Archived redo log files

Query Processing
Parse: checks syntax
Execute:
Fetch: returns data to userDML
StatementsRequires Parse and Execute

Server process reads the data and rollback blocks from the data files.

Blocks that are read then are placed in the Buffer Cache.

Server process locks the data and objects.Server Process records the changes to the rollback and data (new) in theredo log buffer.The Server Process then records the rollback block "old" and updates thedata block "new" in the database buffer cache. Both are marked "dirtybuffers."