Role of the Server Process In Oracle Database

The server process accesses the database on behalf of user processes to read data from datafiles on disk into the buffer cache in the SGA. When SQL statements need to access certain data, Oracle places data into the buffer cache in the following way. If there is an index associated with the table that can be used by Oracle, the blocks for that index will be read into the buffer cache. Oracle then tracks down the appropriate ROWID corresponding to the data needed. As stated earlier, a ROWID is an exact location on a disk for a row of data. There are three components of a ROWID: the data block address, the block row number, and the block datafile number. Commit this information with the following acronym: BRF, for block-row-file. Once the ROWID for a block containing table data is identified, that block is read into memory. If many rows are to be read into memory, then all blocks containing those rows are placed into data block buffers of the database buffer cache. Figure 1 demonstrates the format for ROWID data in Oracle.

Figure 1: ROWID format in Oracle

However, Oracle is not always able to bring data into memory in the most efficient way, as provided by some indexing mechanisms in Oracle. When there is no index on the table, or if the SQL statement does not make use of the index, Oracle has no choice but to read every data block associated with a table into memory to find the block containing the data required for the user request. This method for obtaining data is called a full table scan. In this case, every data block in a table will be read into memory until the row containing the requested data is found.

Once in the buffer cache, that block will be held in the database buffer cache for a certain period of time. That time period is determined by the server process, the mechanism by which Oracle brought the data block into memory. If the data was brought into memory as part of an indexed search, the data block will be held in memory until the space is needed by another block, and the block in the buffer is the least recently used (LRU) block. In this way, frequently used blocks will stay in the buffer cache for a long time, while infrequently used buffers get eliminated to make way for more block storage. This method is often referred to as the LRU algorithm.

   Figure 2: Server Process in Oracle

     

The method used to keep a data block in the buffer cache is slightly different if Oracle must use a full table scan to find the data the user requests. Oracle simply eliminates those buffers almost immediately after they are read by placing them on the least recently used end of the list of blocks slated for elimination from the buffer cache. Oracle doesn’t simply use the LRU mechanism to hold data blocks brought in from full table scans because doing so would quickly fill the buffer cache with blocks from one table, eliminating blocks from other tables that may be in use by other queries. Without a modified LRU algorithm, full table scans will adversely impact performance not only for the query running the full table scan but for the entire database as well.

What often happens in the buffer cache is blocks from indexes on tables commonly accessed stay in the buffer cache longest. Oracle’s modified LRU algorithm facilitates this usage in the following ways. Index blocks are stored in the buffer cache, and frequent use will keep them in the buffer cache. Since an index block for a commonly used table is likely to be used frequently by users, the blocks of the index will likely live in the buffer cache for long periods of time. Storing the blocks of an index in memory is more effective than storing the actual rows of data in memory for two reasons. The first reason is that the index has much fewer blocks than the table, minimizing the number of blocks that need to be stored in memory in order to have easy access to a table. The second reason is that each index block stores searchable column values for table rows, plus an address on disk that will allow Oracle to know almost exactly where on disk to look for the full row data. The buffer cache then acts as a Super Index of the data in the Oracle database.

Please feel free to ask. thank you 🙂

Toufique Khan