Architecture of Oracle Database Management System

Oracle is a Relational Database Management System (RDBMS), which uses Relational Data Model to store its database and SQL (commonly abbreviated as Structured Query Language) to process the stored data. The architecture of Oracle system can be best explained in terms of client/server paradigm. Thus, we will explain the architecture of Oracle server using the structure called instance..

An oracle instance is a complex set of memory structures and operating system processes. It is the Oracle instance, which manages all database activities, such as transaction processing, database recovery, form generation, and so on. The instance structure is loosely styled after UNIX's implementation of multitasking operating system. Discrete processes perform specialized tasks within the RDBMS that work together to accomplish the goals of the instance. Each process has a separate memory block that it uses to store local and private variables, address stacks and other runtime information. The processes use a common shared memory area for processing data concurrently. This memory block is called the System Global Area (SGA). Figure 1 illustrates the architecture of an instance. Each component of the instance is described below.

Figure 1. Oracle instance architecture

System Global Area (SGA)

The SGA is the primary memory component of the instance. It provides memory structure necessary for data manipulation, SQL statement parsing, and redo caching. The SGA is shared, which means that the multiple processes can access and modify the data contained in it in a synchronized manner. The SGA consists of the following components:

The Shared Pool

    Figure 2 illustrates the structure of a shared pool. It contains the library cache, the dictionary cache, and server control structures (such as the database character set).

Figure 2: Oracle Shared pool architecture

Library cache: Stores the text, parsed format, and execution plan of SQL statements that have been submitted to the RDBMS, as well as the headers of PL/SQL packages and procedures that have been executed. For each SQL statement the server first checks the library cache to see if an identical statement has already been submitted and cached. If it has, then the server uses the stored parse tree and execution path for the statement, rather than building these structures from scratch.

    The library cache has shared and private SQL areas. The shared area contains the parse tree and execution path for SQL statement. The private SQL area contains session specific information, such as bind variable, environment and session parameters, runtime stacks and buffers, etc. A private SQL area is created for each transaction initiated, and it is deallocated after the cursor corresponding to that private area is closed. Using these two structures, the server can reuse the information common across all execution of an SQL statement, while session specific information to the execution can be retrieved from the private SQL area.

    The private SQL area is further divided into persistent and runtime areas. The persistent area contains information that is valid and applicable through multiple executions of the SQL statement. The runtime area contains data that is used only while the SQL statement is being executed.

Dictionary cache: Stores data dictionary rows that have been used to parse SQL statements. Information such as segment information, security and access privileges, and available free storage space is held in this area.

The Database Buffer Cache

    The buffer cache is composed of memory blocks. All data manipulated y Oracle server is first loaded into the buffer cache before being used. All data updates are performed in the buffer blocks

    The data movement (swapping and loading) between buffer and disk or other parts of RAM is by least recently Used (LRU) algorithm. The LRU list keeps track of what data blocks are accessed and how often.

    Buffer blocks that have been modified are called dirty and are placed on the dirty list.   The dirty list keeps track of all data modifications made to the cache data that have not been flushed to disk. When Oracle receives a request to change data, the data change is made to the blocks in the buffer cache and written to the redo log, and then the block is put on the dirty list. Subsequent access to this data reads the new value from the changed data in the buffer cache. Dirty data from the dirty list are written to the disk database under deferred update policy.

The Redo Log Buffer

The redo log buffer is used to store redo information in memory before it is flushed to the redo log files on the disk. It is circular buffer.

The Oracle Background Process

The Oracle server process transactions concurrently. Thus, at any time there may be hundreds of simultaneous users performing a number of different operations. To accomplish these tasks, the server divides the entire workload between a number of programs, each of which operates largely independently of one another and has a specific role to play.  These programs are referred to as the Oracle background processes.  The Oracle background processes are:

USER AND SERVER PROCESSES (Snnn)

    Applications and utilities access the RDBMS through a user process. The user process connects to a server process, which can be dedicated to one user process or shared among many. The server process parses and executes SQL statements that are submitted to it and returns the result sets back to the user process. It is also the process that reads data blocks from the data files into the database buffer cache.

    Each process is allocated a section of memory referred to as the Process Global Area (PGA). The contents of the PGA differ depending on what type of connection is made to the database. When a user process connects to the database via a dedicated server process, user session data, stack space, and cursor state information is stored in the PGA. The user session data consists of security and resource usage information; the stack space contains local variables specific to the user session; and the cursor state area contains runtime information for the cursor, including rows returned and cursor return codes. If, however, the user process connects through a shared server process, the session and cursor state information is stored within the SGA.