Oracle Process Structure : Part 1

Recently I read about the Process of Oracle and want to share my knowledge of it with you all. So, let's begin with it.



ORACLE PROCESSES

We all know that a process performs a job or does a task. Similarly, Oracle processes help in the daily functioning of the Oracle instance and Oracle server. It is are broadly classified as in the following.

1. User Process

User processes are generated at the client end. It could be via Oracle provided tools like sqlplus, SQL Developer or a third-party tool like TOAD (Tool for SQL development and administration) and so on. Each user process is linked to a dedicated server process through which its tasks are performed. A User's request and response is handled by the server process. User Process provides the means of communication among application running at the user end and the Oracle instance.

2. Server Process

The server process is a dedicated process for a user process. Its life begins with a user process and ends with it. That means it's session specific. The server process communicates on behalf of the user process with the Oracle database server. Let's understand with an example. Suppose you queried data in a table, the server process:

1. First verifies the syntax of the code,
2. Secondly, it then executes that code, if the data is already not present in the memory then
3. Thirdly, it reads the data block containing the required data in the memory (database buffer cache),
4. Finally, it returns the result of the dataset to the user process, thus displayed to the user via the application.

There are various configurations of the server process depending upon the architecture of the server:

1. Dedicated server: In this configuration the user process and server process share a one-to-one relationship. When even a user process tries to connect, a server process is allocated to handle all the user process requests.
2. Shared server: Under this architecture, multiple users are present and they share the server process, this helps better utilization of the memory. Here, we can use the connection pooling also where we can utilize the existing timeout connection to address other active connections. Further, session multiplexing can also be useful for conducting multiple sessions over the same connection.
3. Database resident connection pooling (DRCP): This is the new feature of the Oracle 11g, it is used for applications that require continuous connection to the database, that ends up using high server resources. To prevent this, a connection broker will be used to allocate a server from a pool of servers, it manages all the connections and once the request is fulfilled it is released.

3. Background Processes

These processes help in the efficient use of the server resources and in day-to-day user request handling. Each background process does a specific task. These processes are automatically created when the Oracle server is brought up and dies with shutdown. We can view all the background processes by querying the v$BGPROCESSES view. The following are the background processes that are mandatory for Oracle to function.

1. Database Writer (DBW)
   
   The database writer writes the modified data from the database buffer cache to the physical disk datafiles. When the space in the database buffer is low the DBW process writes the dirty data to the disks making more space available for the use in the database buffer. The DBW uses the Least Recently Used algorithm for freeing the space. Further the DBW may perform the following writes:
   
   
   • Periodically with the checkpoint occurrence.
   • Asynchronously along with other processing
   
   The DB_WRITER_PROCESSES parameter helps to initialize the number of DBW, there can be a maximum of 20 such processes, if not specified then the numbers are automatically determined by the number of processors and memory. The DBW writes a dirty buffer to disk on the following terms:
   
   
   • Whenever the Databuffer is full and there is no reclaimable space left, the DBW writes a dirty buffer to disk
   • DBW writes data to the disk at the occurrence of a check point.
   • Also every three seconds.
   • The DBW does not write at every commit especially if it's occurring very frequently, it will write them as a batch to prevent frequent I/O.
     
     
      
2. Log Writer
   
   This process manages the space of the Redo Log Buffer. At every DML the redo log is generated and space in the redo log buffer is utilized. The LGWR process writes the changed in redo log buffer to the redo log file on the disk. In the multiplexed redo log environment the LGWR writes the committed changes to all the members. LGWR trace files are generated for the errors thrown by the LGWR. LGWR uses group commit for the heavy transaction activities. The Log Writer writes to the redo log under the following conditions:
   
   
   • Redo Log buffer is one-third full.
   • Oracle uses write ahead protocol, under which when DBW signals redo records are to be written to the disk, all redo reflecting the changes in the data block must be written to the disk before modifying the datafiles on the disks.
   • Every 3 seconds
   • Once the redo buffer is written to the disks it can be used for backup and data recovery.
     
   
   Further, writing data to the redo log file even before the actual data is modified is called the fast commit mechanism.