Database Buffer Cache In Oracle

In today’s article, we will be cover about the features of the Database Buffer Cache, the largest memory area in the SGA.

• The most important task of this area is to optimize the amount of physical I/O.

• Data blocks in the Data file that are usually read by users are kept in this area.

• Oracle searches the queried data in this area before going to the data files in the physical disk space.

If the queried data has been queried by another user before, Oracle transmits the data to the user from this field.

• If users perform DML operations such as update or insert, the changed data blocks are written to this area before the data files on the physical disk.

These data blocks that change in the buffer area are called “Dirty Blocks”.

Dirty Block; These are database blocks that have been changed by the user, written to the database buffer cache, and not yet written to the data files on the disk.

The database marks these blocks with a “checkpoint” before using them.

These blocks are called “Clean Blocks” after they are written to the data files on the disk.

• Blocks used by any user in the database are pinned to memory.

The session using the pinned blocks can modify these blocks.

Not all sessions can change the pinned block at the same time.

• The DBW(n) background process carries out the communication between the Database Buffer Cache and data files.

• Database Buffer Cache is emptied with the following command.

In this case, dirty blocks are saved to data files with the DBW(n) process.

• LRU (least recently used) algorithm runs in the database buffer cache.

The least used data is deleted from the buffer cache and the most used data is preserved.