ORACLE ARCHITECTURE

 Oracle Server Architecture

Oracle Server is an object relational database management system that provides an open, comprehensive, integrated approach to information management.

Diagram of Oracle Server Architecture

Oracle Instance:

Oracle Instance is a combination of Memory structures and Background Processes used to manage the Database. One instance can open and use only one Database. There are two basic Memory structures in Oracle’s Instance.

1.    SGA (System Global Area)

2.    PGA (Program Global Area)

1.  SGA:

SGA consists of several items like buffer cache, shared pool, and Redo log buffers. SGA Contains the data control information for the Oracle server. It is allocated in the virtual memory of the computer where the Oracle server resides. The SGA consists of several memory structures

1.    Buffer Cache.

2.    Shared Pool.

3.    Redolog Buffer.

1.    Buffer Cache:

The Database Buffer cache is used to store the recently accessed data. The Buffer Cache has two purposes: to Improve performance for subsequent repeated select statements on the same data, and to allow oracle users to make changes quickly in memory. Oracle writes those data changes to the disk later. The database buffer cache is divided into dirty lists (Buffer that have been modified and waiting to be written to disk) and least recently used (LRU) lists. Buffers those are unmodified are used as free lists.

The size of the Buffer Cache can be determined by the

Initialization parameter

DB_BLOCK_BUFFERS

The size of the buffer is based on the initialization parameter

DB_BLOCK_SIZE

When a query is being executed, the server process looks in the database buffer cache for any blocks it needed. If the blocks are not found in the buffer cache, then it reads the data from the dbf files and places a copy in the buffer cache. If any subsequent same requests are issued by the user, oracle gives the data from the buffer cache itself.

2.    Shared Pool:

The shared pool is used to store the most recently executed SQL statements and most recently used data from the data dictionary. There are two mandatory structures in the Shared pool.

I.     Library Cache.

II.  Data Dictionary Cache.

Library Cache:

Library Cache is used to store the parsed SQL statement text and statement’s Execution plan for reuse. Library cache stores the most recently used SQL statements in a memory structure called a shared SQL area. The Shared SQL area consists

·       The text of SQL Statements.

·       The parsed tree – the Compiled version of Statement.

·       Execution Plan - The steps to be taken to execute the

Statement

Data Dictionary Cache:

It is also called as Dictionary Cache or “row Cache”, which is used to store recently accessed information from the Oracle Data Dictionary such as table and column definitions, usernames, passwords, and privileges.

During the parse phase, the server process looks for the information in the Dictionary cache to resolve the object names specified in the SQL statement to validate the access privileges. If necessary the server process lodes the required information from the base tables into the data dictionary cache.

We can determine the share pool size by the parameter

SHARED_POOL_SIZE

3.    Redolog Buffer:

Redolog Buffer is used to store the Redolog entries generated by the DML statements. A Redolog entry is a small amount of information produced and saved by oracle to reconstruct, or redo, changes made to the database by insert, update, create, alter and drop statements. If some sort of failure occurs, the DBA can use redo information to recover the Oracle database to the point of database failure.

The size of the Redolog buffer is determined by the initialization parameter

LOG_BUFFER

There are two optional components in Oracle’s SGA. They are

1. Java Pool : Used to store Java Code.

2. Large Pool : Used to store large memory structures not

Directly related to SQL statements processing,

Such as data blocks copied during the backup and

Restore operations.

  

Large Pool

This area is only used if shared server architecture (multi-threaded server - MTS) is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query.

In general, large pool provides memory allocations for:

• Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
   
• I/O server processes
   
• Oracle backup and restore operations

Streams Pool

This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly, you need to specify the size of the pool in bytes using the STREAMS_POOL_SIZE initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

Simple form of SGA

2.  PGA:

The Other Memory structure in Oracle instance is called the Program Global Area (PGA). PGA helps user processes execute by storing information like bind variable values, sort area, and the other aspects of cursor Handling.

PGA contains

1. Sort Area: This is used to perform any sort operations.

2. Session Information: Such as user privileges for the Session.

3. Cursor State: This indicates the stage in the processing of various cursors that are used in the current session.

4. Stack Space: Contains the Session Variables.

PGA is allocated when a process is created and de-allocated when the Process terminates.

User Process:

When a user starts a tool such as SQL*Plus or D2K a user process is created on the client Machine. It starts when the tool is started and terminated when the user exits or closes the application. The User process includes the User program Interface (UPI). The UPI generates the necessary calls to the Server Process.

Server Process:

When a user tries to logon to the Oracle server by specifying a Username and a Password, a server process is created on the Machine where Oracle server runs. The Server process communicates with the oracle server on the behalf of the user process that runs on the client. There are two ways that a DBA can setup Oracle to run server processes.

1.    Dedicated Servers. b. Shared Servers.

Dedicated Servers:

In dedicated server setup, every single user process connecting to oracle will have its own server process. Suppose there are 100 users connected to Oracle server, then there will be 100 server processes in the Oracle Server.

In the Dedicated server mode

Number of Server Processes = Number of User Processes

Advantages:

·       Every requested user will get their data retrieval requests acted upon immediately.

Disadvantages:

1.    There will be additional memory and CPU required for the Machine running the Oracle Database.

2.    Most of the time the Oracle Server will sit idle, if a particular user is not submitting any queries.

Shared Servers

In this setup, several concurrent users are served by a single server process. Oracle manages this setup by a network process called dispatcher.

The User processes are assigned to a dispatcher, and the dispatcher puts the user request for data into one queue, and the shared server process fulfills the requests, one at a time. This configuration can reduce the memory and CPU burden on the machine that hosts Oracle, as well as limiting the server process idle time.

Disadvantages:

1. During periods of high database use, the user processes have to wait for the availability of server process.

How the Users are Connected to the Oracle Server

A Network process called Listener process resides in the Oracle Server’s Machine. The Listener process listens for users trying to connect to the Oracle database via the Network. When a user connects to a machine hosting the Oracle database, the listener process will do one of two things.

If dedicated server process is being used, then the listener tells oracle to generate a new Server process and then assigns the user process to that dedicated server process.

If MTS is being used, the listener sends the user process to the Dispatcher. The dispatcher places the users request on the request queue, where it is picked by the available server process. The request queue is common for all dispatchers in SGA.

The server process checks the request queue for any new requests and picks up new requests on a first-in-first-out basis. When the server completes the requests, it places the results on the calling dispatcher’s response queue. Each dispatcher has its own response queue in SGA.

Processing a Query

1.    When a user submits a valid SQL statement, first the server process searches whether a parsed copy of this statement is exists in Shared pool or not.

2.    Then it checks for the validity of the issued SQL statement.

3.    Checks in the data dictionary whether all the columns and tables referred in this statement exist or not. (Performs these checks by getting the data from the Base tables into the Data Dictionary Cache).

4.    Now the statement acquires parse locks on objects referenced in this statement so that their definitions don’t change while the statement is parsed.

5.    Oracle ensures that the user attempting to execute this statement has enough privileges in the database.

6.    After this Oracle creates an Execution plan for the statement and places it into the library cache. (Execution Plan – the optimal method for executing the SQL Statement. Optimizer function determines the execution plan). If this parse is already exists then oracle skips this step.

7.    Oracle performs all the processing to execute the select statement. At this point the server process will retrieve the data from the disk into the buffer cache.

8.    Once the statement has been executed, all data returned from Oracle is stored in a Cursor. The data is then placed in bind variables, row by row and returned to the user process.

Processing a DML Statement

The DML statements execution is similar to the normal SQL statements.

1. Oracle creates an Execution plan to execute the DML statement and places that in the library cache. If the execution plan already exists for this statement, Oracle skips this step. If the Data blocks are not present in the Buffer cache then the

Server process reads the data block from the database into the buffer cache.

2. Oracle performs all the processing steps to execute the DML statement. For Update or Delete statements, the server process will retrieve data from the disk into the buffer cache, implicitly acquires a lock on the data to be changed and then makes the specific changes in the buffer cache.

3. When executing a DML statement Oracle writes the old and new versions of data to the rollback segment acquired from the transaction. In the Redolog buffer Oracle records the changes to be made to the rollback and data.

The rollback block is used to store the before image of the data, so that the DML statements can be rolled back if necessary. New values are populated in the data block buffers

4. The Server Process records the before-image to the Rollback segments and updates the data block. Both the changes are done in the database buffer cache. Any changed blocks in the buffer cache are marked as dirty blocks – that is the buffers those are not same as the corresponding blocks on the disk.

For Every DML Statement, the user process must write a redo entry to the Redolog buffers. In this way Oracle can recover a data change if damage is later done to the disk files containing the Oracle data.

Background Processes:

The Background processes in an instance perform functions those are needed to service the request from different concurrent users. Each Oracle Instance uses different background processes depending on the configuration.

There are several types of processes running al the time in Oracle. The most important one from the users prospective is the Server Process. The Server Process acts on the user’s behalf to pull Oracle Data from the disk into the buffer cache.

There are some mandatory and optional background processes in Oracle’s Instance. They are

1.    SMON (System Monitor)

2.    PMON (Process Monitor)

3.    LGWR (Log Writer)

4.    DBW0 (Database Writer)

5.    ARC0 (Archive Process)

6.    CKPT (Checkpoint Process)

a. SMON:

SMON is one of the mandatory background processes in Oracle Instance. If Oracle Instance fails, any information that is written in SGA that has not been written to the disk will be lost. After the loss of instance, the background process SMON automatically performs recovery when the database is started. The instance recovery consist the fallowing steps.

▫ Any Un-committed transactions will be rolled back.

▫ Any committed transactions are recorded in the Redolog buffer, The changes are written to the database files. In this process SMON reads the data from the Redolog buffers and applies those changes to the database files. This process completely recovers these transactions.

▫ If any locks are applied on the database tables before the instance crash, now they are released.

SMON also performs some maintenance functions

1. It Combines, or coalesces, adjacent areas of free space in the data files.

2. De-allocates the temporary segments to return the free space in the datafiles.

b. PMON:

PMON monitors the user processes on the database to make sure that they work correctly. If for any reason a user process fails during its connection to Oracle, PMON will clean up the activities those are related to the user process and make sure that any changes that it made to the system will be rolled back.

PMON is responsible for

·       Rolling back the disconnected user process’s transactions.

·       Releasing any locks those are acquired by the disconnected user process.

·       Freeing all the resources used by the disconnected user process.

c. LGWR:

Logwriter writes entries from the Redolog buffer to the Redolog files. The logwriter performs sequential writes to the Redolog files under the fallowing situations.

·       When the Redolog buffer is 1/3 filled.

·       When the timeout occurs (for every 3 seconds)

·       Before the DBWR writes the dirty blocks to the database files (DBFs).

·       When a transaction commits.

The LGWR confirms the COMMIT only after the redo is written to the disk. If there are two members in a Redolog file then LGWR writes the same data in both the members.

d.DBW0:

The server process records the changes to the rollback segments and to blocks in the buffer cache. The database Writer writes the dirty

Blocks from the buffer cache to the database files (DBFs). DBW0 ensures that a sufficient number of free buffers are available in the database buffer cache.

DBW0 is invoked when

1.    The Number of dirty buffers reaches to a threshold value.

2.    When a process scans a specified number of blocks when scanning for the free buffers and can’t find any.

3.    When timeout occurs (for every 3 seconds).

4.    When a checkpoint occurs.

e.ARC0:

The Archive process (ARC0) is an optional background process which is invoked when the database is in Archivelog mode only. If the database is in archive log mode, whenever a Redolog file fills, the Oracle server begins writing to the next online Redolog file and then the ARC0 process is invoked to archive the filled Redolog file.

The ARC0 process initiates the backing up or archiving of the filled online Redolog group at every log switch.

These archived logfiles are very important to recover the database in the case of a disk crash.