Brief architectural overview of several RDBMS - Oracle 11g

Details

Published on Saturday, 04 January 2014 17:53

Oracle 11g

   The client process establishes communication with the Oracle RDBMS instance and the instance stores session-specific information for the client in an area called PGA (Program Global Area). An Oracle server instance is a complex set of memory structures and operating system processes and it is made up of:

• Oracle’s main memory structure called SGA (System Global Area). 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 Shared Pool,  Database buffer cache, Redo Log Buffer and Multithread server structures. The shared pool caches the most recently used SQL statements that have been issued by database users. The 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 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 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 is used to store redo information in memory before it is flushed to the redo log files on the disk. It is circular buffer. Optional SGA components include: the Java Pool (Cache the most recently used Java objects and application code when Oracle’s JVM option is used), the Large Pool (caches data for large operations such as the Recovery Manager (RMAN) backup and restore activities and Shared Server components and Streams Pool – caches the data associated with queued message requests when Oracle’s advanced queuing option is used;

• The Oracle background processes. The Oracle server processes 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.  They are:

• SMON (System Monitor) -  mandatory processperforms instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting;
  
  
• PMON (Process Monitor) – mandatory process that cleans up failed user database connections monitor;
  
  
• Database Writer (DBWn) – mandatory process that writes modified database blocks from the SGA’s Database Buffer Cache to the datafiles on disk;
  
  
• Log Writer (LGWR) – mandatory process that writes transaction recovery information from the SGA’s Redo Log Buffer to the online Redo Log files on disk;
  
  
• Checkpoint (CKPT) – mandatory process that updates the database files following a Checkpoint Event;
  
  
• Dispatcher processes (Dnnn) – a dispatcher process passes user requests to the SGA request queue and returns the server responses back to the correct user process;
  
  
• Shared Server Processes (Snnn) – serve multiple client requests in the shared server configuration; 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 asProcess 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;
  
  
• Archiver (ARCn) – optional process that copies the transaction recovery information written to disk by LGWR (log writer) to the online Redo Log files and to a secondary location in case it is needed for recovery. Nearly all production databases use this optional process;
  
  
• Recoverer (RECO) – optional process that recovers failed transactions that are distributed across multiple databases when using Oracle’s distributed database feature;
  
  
• Job Queue Monitor (CJQn) – assigns jobs to the Job Queue processes when using Oracle’s job scheduling feature;
  
  
• Memory Manager (MMAN) – Manages the size of each individual SGA component when Oracle’s Automatic Shared Memory Management Feature is used.

• Next >