How is Oracle DMBS

author

The database buffer cache stores data blocks from the database. The smallest unit of the Oracle database that can be read or written is the Oracle database block. For example, rows from tables are stored in the database blocks. If a data record is searched for in a table using an SQL query, the table block in which this row is located is loaded into the database buffer cache, but only the row searched for is displayed.

By loading the entire block, all data records that are in this block are also loaded. If the same or a different user needs data that is also in this block, the block does not have to be reloaded from the disk system, which increases the access speed. With an optimal size configuration of the database buffer cache, all important movement data of the database can be kept in the main memory.

So that Oracle can recognize which blocks are needed more than others, the presence of the blocks in the database buffer cache is managed by an LRU (Least Recently Used) algorithm. This means that the blocks that are accessed frequently are kept longer in main memory than those that are only loaded once and then no longer needed. This LRU algorithm then drops blocks that are no longer required from the database buffer cache to make space for new ones.

The block size is determined during the installation of a database and can be between 2 and 32 kilobytes. It is no longer possible to change the block size after installing a database and can only be achieved through a new installation. The set standard block size is 8 KB.

The block size should be set depending on the intended operating mode of a database: In so-called OLTP databases (On-Line Transaction Processing), smaller blocks are generally preferred, in OLAP databases (On-Line Analytical Processing) larger blocks are preferred.

OLTP databases are characterized by a high transaction rate, the data changes within the transactions are small. In addition, there are many queries entering the database, the results of which are small. With these databases, small block sizes are usually preferred in order to have to load a few additional data records per block per data record access. OLTP systems are, for example, ERP systems (enterprise resource planning, personnel planning, capital, operating resources, sales, marketing, finance and accounting) or CRM systems (customer relation management, systems for customer care).

OLAP databases are filled with data at certain intervals and are used to analyze this data. Since large amounts of data are processed in these databases, larger blocks are preferred because reading a data record that is located in such a large block can load and process many additional data records at the same time.